如何：正确使用PHP将数据编码为JSON格式，并使用jquery / ajax请求数据

Question

I am trying to create an address book of sorts.

I can successfully connect to the database and insert data with a php script.

I have even managed to display json encoded data of my table rows, though I don't know if I am doing it right.

What I am actually trying to accomplish:

1. I would like to be able to make an ajax request for say, and ID, then get back all of that ID's corresponding data, (wrapped in Json - At least I think it needs to be..).
2. With the ajax script, I would like to be able to save the returned corresponding data to an input field in an html file.

I would also like to know if it would be better to try to return HTML to the ajax call, and input the data into the html input fields that way?

So far I am having limited success, but here is what I have so far...

I have a DB connection script:

$host = "localhost";
$user = "user";
$pass = "pass";
$db = "data_base";

$mysqli = new mysqli($host, $user, $pass, $db);

if($mysqli->connect_error) 
 die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

return $mysqli;

A mysql ISAM DB with the following columns:

    id, user, pass, nickname, address, facebook, twitter, linkedin, youtube
    ID should be unique
    User is an index
    Pass is an index
    nickname is an index
    address is primary - though its possible that id should be...
    Facebook, Twitter, Linkedin, and Youtube are all indexes. 

Note: I would be happy to change index, primary, etc as somebody sees fit...

EDITED!**Now my query page:

error_reporting(E_ALL); ini_set("display_errors", 1);
include 'db/dbcon.php';
//Start connection with SQL
$q = "SELECT * FROM `cfaddrbook` WHERE key = '111111'";
$res = $mysqli->query($q) or trigger_error($mysqli->error."[$q]");
$array = array(); // initialize
while($row = $res->fetch_array(MYSQLI_BOTH)) {
$array[] = array(
'key' => $row[0],
'username' => $row[1],
// ... continue like this
);
}
header('Content-Type: application/json');
echo json_encode($array);
$res->free();
$mysqli->close();

Now, the above script seems to work fine. At least it displays just fine when loading the php page in the browser.

But when I make an ajax call with this script:

$(document).ready(function(){ 
$.ajax({
        type: "POST",
        url: "queries.php",
        dataType: 'json',
        data: "",
        cache: false,
        success: function(result)
            {
                var cfkey = result[0];
                var user = result[1];
                alert("cfkey:" + cfkey + "user:" + user);
            }
    });
});

After loading this code, the chrome console states that the server returned with error 500.

Again, what I am trying to accomplish:

1. I would like to be able to make an ajax request for say, and ID, then get back all of that ID's corresponding data, (wrapped in Json - At least I think it needs to be..).
2. With the ajax script, I would like to be able to save the returned corresponding data to an input field in html.

EDIT: Finally figured out that the problem I was discussing with Majid was with the SQL query. key needed to be need to be wrapped in ` characters.

Answer 1

After you execute your query and the resultset is available in $res you could just build up your array, no need for a separate foreach:

$array = array(); // initialize
while($row = $res->fetch_array(MYSQLI_BOTH)) {
  $array[] = array(
    'id'       => $row[0],
    'username' => $row[1],
    'password' => $row[2],
    'nick'     => $row[3],
    'addr'     => $row[4],
    'facebook' => $row[5],
    'twitter'  => $row[6],
    'linkedin' => $row[7],
    'youtube'  => $row[8]
  );
}
header('Content-Type: application/json');
echo json_encode($array);

Also note that this way, your json will have keys, so to consume it you should change:

success: function(result) {
  var cfkey = result[0];
  var user = result[1];
  alert("cfkey:" + cfkey + "user:" + user);
}

To

success: function(result) {
  var cfkey = result.id;
  var user = result.username;
  alert("cfkey:" + cfkey + "user:" + user);
}

Or simply do

$.getJSON('queries.php', {cfkey: $("#cfkey").val()}, function(result) {
  // we have multiple results
  $.each(result, function(i,r) {
    console.log("cfkey:" + r.key + "user:" + r.username);
  });
});

Edit: added header as pointed out by @amurrell