homepage Welcome to WebmasterWorld Guest from 50.17.174.94
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Visit PubCon.com
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
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>");

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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved