How to: Properly use PHP to encode data into JSON

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

Answer 2

Your script that outputs JSON is writing several valid JSON strings (one for each database row), but they don't add up to a valid JSON file. A JSON file should represent one JSON object.

If you want to pass an ID and get one database row back, you have to add that ID to the data part of your AJAX call, and modify queries.php to pass that id from its $_POST array into the WHERE part of your MySQL query. Then, you'd only output one JSON-encoded object rather than many, which would be a valid JSON file.

(Alternately, you could json_encode() the entire $rows array rather than each $row individually if you want the whole table back.)

Also, if you json_encode() a string-indexed array in PHP, you read its properties in Javascript by name, not by index. You've gone through the trouble of naming your keys in PHP, then switch back to trying to reference them by their 0-based index in Javascript. You can pick one way or the other, but you can only pick one!

Answer 3

I believe you are expecting queries.php to return json (to your ajax) and thus you need content header types in your queries.php!

header('Content-Type: application/json');

Answer 4

You need more useful error messages. Try adding the following lines at the beginning of your code.

error_reporting(E_ALL); ini_set("display_errors", 1);