| How to use JSON with multiple rows from MySQL
|
LinusIT
msg:4499791 | 3:04 pm on Sep 25, 2012 (gmt 0) |
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
msg:4499999 | 3:32 am on Sep 26, 2012 (gmt 0) |
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
msg:4502508 | 3:14 pm on Oct 1, 2012 (gmt 0) |
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
msg:4502527 | 3:43 pm on Oct 1, 2012 (gmt 0) |
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
msg:4503155 | 8:11 pm on Oct 2, 2012 (gmt 0) |
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
msg:4503306 | 7:02 am on Oct 3, 2012 (gmt 0) |
using eval function you can read these json data:
result = eval(result);
result[0].id;
I hope it will work.
|
Fotiman
msg:4503477 | 2:08 pm on Oct 3, 2012 (gmt 0) |
Don't use eval, use JSON.parse().
|
LinusIT
msg:4503531 | 4:15 pm on Oct 3, 2012 (gmt 0) |
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>"); |
|
|
|
|
