JSON. Parsing array of arrays. How?

Question

How can I modify my existing JQuery code to loop through a JSON array of arrays? For example, when PHP returns a JSON result from MySQL database containing more than 1 row.

Here is my code. It only works with a single row result.

$(function(){
 $('#btn_select_account').live('click', function() {
 // URL...
 $.getJSON('api.php?',
 // Parameters...
 { call: 'select_account', p0: 'suchislife801' },
 function(result){
 // For each item inside array...
 $.each(result, function(index, value) { 
 // Append to this html element
 $('#output').append(index + ': ' + value + '<br />').fadeIn(300); 
 });
 });
 });
});

The following PHP code....

 function qry_select_account($pk_account) {
 // Global variables
 Global $db_host, $db_user, $db_pass, $db_name; 
 // Connect to database server
 $dbc = mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
 // Select target database
 mysql_select_db($db_name) or die(mysql_error());
 // suchislife801 <--- Selects account information
 // Run query
 $sql_qry = mysql_query("SELECT * FROM tblaccount
 WHERE pk_account = '$pk_account'") or die(mysql_error());
 // SQL Criteria AND acc_confirmed = 'y' AND acc_locked = 'n'
 // Fetch table row for this user
 $row = mysql_fetch_row($sql_qry);
 print json_encode($row);
 mysql_free_result($sql_qry);
 // Close Connection
 mysql_close($dbc);
 }

Generates the following response:

["20","1","suchislife801","Happy","My first entry title.","Body of my first entry.","2012-04-03","15:06:38","n","0"]

I am trying to change it so that it works with the following PHP code...

 function qry_select_last5_entries_for_user($ent_user) {
 // Global variables
 Global $db_host, $db_user, $db_pass, $db_name; 
 // Connect to database server
 $dbc = mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
 // Select target database
 mysql_select_db($db_name) or die(mysql_error());
 // suchislife801 <--- Selects last 5 entries for user
 // Run query
 $sql_qry = mysql_query("SELECT * FROM tblentry 
 WHERE ent_user = '$ent_user' ORDER BY ent_date DESC , ent_time DESC LIMIT 5") or die(mysql_error());
 // Fetch table rows for this user
 while ($row = mysql_fetch_array($sql_qry, MYSQL_NUM)) {
 print json_encode($row);
 } 
 mysql_free_result($sql_qry);
 // Close Connection
 mysql_close($dbc);
 }
["20","1","suchislife801","Happy","My first entry title.","Body of my first entry.","2012-04-03","15:06:38","n","0"]["19","1","suchislife801","Happy","My first entry title.","Body of my first entry.","2012-04-03","15:06:37","n","0"]["18","1","suchislife801","Happy","My first entry title.","Body of my first entry.","2012-04-03","15:06:36","n","0"]["17","1","suchislife801","Ugly","My first entry title.","Body of my first entry.","2012-04-03","15:06:35","n","0"]["15","1","suchislife801","Lazy","My first entry title.","Body of my first entry.","2012-04-03","15:06:34","n","0"]

• Kevin B: Can you post a sample of the json output, or possibly the php that generates it?
• suchislife: I have just included the samples for you.
• Kevin B: Your php doesn't seem to be returning valid json.
• suchislife: that is valid JSON. Each row returned by database is encapsulated in its own [ ]. PHP clode used print json_encode($row); Keep in mind I have this inside while loop in my php code.
• Kevin B: It is not valid json, valid json would be more like: [["foo","bar"],["foo":"bar"]]

Answer 1

In your php, modify your loop to create an array, and then echo the json_encode result of that array.

The output should look like this:

[["20","1","suchislife801","Happy","My first entry title.","Body of my first entry.","2012-04-03","15:06:38","n","0"],["19","1","suchislife801","Happy","My first entry title.","Body of my first entry.","2012-04-03","15:06:37","n","0"],["18","1","suchislife801","Happy","My first entry title.","Body of my first entry.","2012-04-03","15:06:36","n","0"],["17","1","suchislife801","Ugly","My first entry title.","Body of my first entry.","2012-04-03","15:06:35","n","0"],["15","1","suchislife801","Lazy","My first entry title.","Body of my first entry.","2012-04-03","15:06:34","n","0"]]

Then, in jQuery, you would output it like this:

var outHtml = "";
$.each(result, function(index, value) { 
 outHtml += "Entry " + index + ":<br />";
 outHtml += $.map(value,function(i,value){
 return i + ": " + value + "<br />";
 }).join("");
 outHtml += "<br /><br />";
});
$("#output").html(outHtml);

Demo: http://jsfiddle.net/R8wvn/

Edit:
php to get desired result(not tested):

var $out = array();
// Fetch table rows for this user
while ($row = mysql_fetch_array($sql_qry, MYSQL_NUM)) {
 array_push($out,$row);
} 
print json_encode($out);

• suchislife: Dang it. I see what you mean. The while loop is generating 1 array at the time. It needs to instead, created an array of arrays and be encoded to JSON out side the loop.

Answer 2

Since it looks like value will be a json object as well, you can do this:

$.each(result, function(index, value) { 
 for (var key in value) 
 $('#output').append(index + ': ' + key + ': ' + value[key] + '<br />').fadeIn(300);
)};

Fiddle Demo: http://jsfiddle.net/StefanCoetzee/FZ8f2/3/

EDIT: Just change your php code to this:

$arr_results = array();
// Fetch table rows for this user
while ($row = mysql_fetch_array($sql_qry, MYSQL_NUM))
 $arr_results[] = $row; // quicker than using array_push()
echo json_encode($arr_results); // *echo* is apparently slightly faster than *print*

This results in valid json output.