homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / JavaScript and AJAX
Forum Library, Charter, Moderator: open

JavaScript and AJAX Forum

How to use JSON with multiple rows from MySQL

 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({
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 {
$(".main-content").empty(); //EMPTY THE PAGE READY FOR THE DATA


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



 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.


 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.


$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);
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":"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.


 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.


 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.



 7:02 am on Oct 3, 2012 (gmt 0)

using eval function you can read these json data:

result = eval(result);

I hope it will work.


 2:08 pm on Oct 3, 2012 (gmt 0)

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


 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>');


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / JavaScript and AJAX
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved