How to use JSON with multiple rows from MySQL

[LinusIT]

Hi all,

I hope the title has got the general question over. I'm using a JqueryUI datepicker, once I've selected a date, this is sent through an AJAX call to a php file to select the data based on that date. From what I can tell this is working fine apart from I can't get all the rows returned back over into the AJAX call. I have used "alert" to show me what's being returned.

AJAX:

$. ajax({
type: 'POST',
url: 'includes/process.php',
data: dataString,
dataType: "json",
cache: false,
success: function(response) {
alert (JSON.stringify(response)); //SHOW ME THE DATA
if (response.error) {
alert("There was an error");
} else {
alert("success");
$(".main-content").empty(); //EMPTY THE PAGE READY FOR THE DATA
//alert("Success");
}
}
});

PHP

if ($num_rows > 0) {
while ($row=mysql_fetch_array($result)) {
$array_data = array('id'=>$row[0], 'ref'=>$row[3], 'inn'=>$row[4], 'out'=>$row[5], 'detail'=>$row[6]);
}
echo json_encode($array_data);
} else {
$arr = array("error" => "true");
echo json_encode($arr);
}

Hopefully someone can help me.. Thanks

[Fotiman]

In your PHP code, you're replacing the value of $array_data each time through the loop instead of appending to it. I think you want an array of arrays.

[LinusIT]

I think I'm getting somewhere now, I've got the following code which produces an array of data.

Code:

$myarray = array();
while ($row = mysql_fetch_assoc($result)) {
$id = $row['tl_id'];
$ref = $row['tl_ref'];
$in = $row['tl_in'];
$out = $row['tl_out'];
$detail = $row['tl_detail'];

$myarray[] = array('id' => $id, 'ref' => $ref, 'in' => $in, 'out' => $out, 'detail' => $detail);
}
var_dump($myarray);
echo json_encode($myarray);

Which gives me the following:

array(3) {
[0]=> array(5) { ["id"]=> string(3) "892" ["ref"]=> string(4) "Test" ["in"]=> string(4) "5.00" ["out"]=> string(4) "0.00" ["detail"]=> string(0) "" }
[1]=> array(5) { ["id"]=> string(3) "890" ["ref"]=> string(4) "Test" ["in"]=> string(4) "0.00" ["out"]=> string(4) "1.00" ["detail"]=> string(0) "" }
[2]=> array(5) { ["id"]=> string(3) "888" ["ref"]=> string(13) "Start Balance" ["in"]=> string(8) "12345.00" ["out"]=> string(4) "0.00" ["detail"]=> string(0) "" } }
[
{"id":"892","ref":"Test","in":"5.00","out":"0.00","detail":""},
{"id":"890","ref":"Test","in":"0.00","out":"1.00","detail":""},
{"id":"888","ref":"Start Balance","in":"12345.00","out":"0.00","detail":""}]

I have split the lines to make it more readable. I've noticed that it is giving me the data twice. I think I need to change the PHP array code first before I look at the ajax/json part.

Can anyone assist me with that please.

[Fotiman]

It's giving you the data twice because you're doing both var_dump and echo, both of which output. If you remove the var_dump, I think you'll have the result you were trying for.

[LinusIT]

That's much better, thanks for that! Learning all the time

It is now returning what I think I can use

[{"id":"888","ref":"Start Balance","in":"12345.00","out":"0.00","detail":""},{"id":"890","ref":"Test","in":"0.00","out":"1.00","detail":""},{"id":"892","ref":"Test","in":"5.00","out":"0.00","detail":""}]

Can you assist with how to read this on the main page please. Even just a point in the right direction, I love a challenge.

Thanks

[Alok07]

using eval function you can read these json data:

result = eval(result);
result[0].id;

I hope it will work.

[Fotiman]

Don't use eval, use JSON.parse().

[LinusIT]

I've got it working now, I used $each(response, function(key, val) in the end.

I have one more question. Using the code below the results are formatted nicely but it isn't a seamless table. Is there anyway of changing the code so it formats the results correctly into a table.

$(".main-content").html("<table id=\"till\">" +
"<tr><th class=\"w120\">Reference</th><th class=\"w80\">In</th><th class=\"w80\">Out</th><th class=\"w250\">Details</th></tr>");

$.each(response, function(key, val) {
$(".main-content").append('<tr><td id="' + key + '">' + val.ref + '</td><td>' + val.inn + '</td><td>' + val.out + '</td><td>' + val.detail + '</td></tr>');
});

$(".main-content").append("</table>");