AJAX PHP JSON encode not returning all results

Question

I'll be brief... I'm about to jump

not all results are working

in the document header I am including jquery-1.7.2.min.js -

my query:

"SELECT tableA.columns_xyz..., tableB.columns_xyz..., tableC.columns_xyz 
FROM tableB
JOIN tableC ON tableB.x = tableC.x
JOIN tableA ON tableB.x = tableA.x
WHERE tableA.x = '$query_value'
GROUP BY tableA.y
ORDER BY tableA.y DESC 
LIMIT 1";

PROBLEM 1:

change LIMIT 1 to LIMIT 5 or anything >1 and the query executes fine in phpmyadmin but not via ajax/json/php

the php:

$sql = above...
$result = $mysqli->query($sql);
while($row = mysqli_fetch_array($result)) {
echo json_encode(array($row['x'],$row['y'],....$row['z']));
$mysqli->close();

the javascript:

$.ajax ({
    url: 'script.php',
    type: "post",
    dataType: "json",
    data: {value : $("#searchbox").val()},
    success: function(data) {
        var a = parseFloat(data[0]);
        var b = parseFloat(data[1]);
        var c = data[2];

            document.getElementById("blah1").innerHTML = a;
            document.getElementById("blah2").innerHTML = b;
            document.getElementById("blah3").innerHTML = c;
       }
});

the html:

<td><span id="blah1"></span></td>
<td><span id="blah2"></span></td>
<td><span id="blah3"></span></td>

PROBLEM 2:

some elements are printing to page, some aren't. what am I doing wrong ?

PROBLEM 3:

I know I should be able to express document.getElementById("blah1").innerHTML; as $('#blah1'); but $('#blah1'); is just not working... I don't know why...

Wern Ancheta · Accepted Answer · 2012-07-14 12:02:03Z

1

Store each row in an array then use json_encode once:

$store = array();
while($row = mysqli_fetch_array($result)) {
    $store[] = array('x'=>$row['x'], 'y'=>$row['y'], 'z'=>$row['z']);
}
echo json_encode($store);

Then you can use JSON.parse on the client-side:

var store = JSON.parse(data)

And then just loop through that:

for(var x in store){
    $('#blah1').html(store[x]['x']);
}

You're already using jquery so why don't you make full use of it by converting this:

document.getElementById("blah1");

To its jquery version:

$('#blah1');

Just make sure that those elements are unique since you're using ID's

edited Jul 14, 2012 at 12:02

answered Jul 14, 2012 at 11:49

Wern Ancheta

23.4k38 gold badges105 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

Kyokasuigetsu thank you for your response. I am trying to get it working now. I was aware of the $ shortcut for document.getElementById some reason it only worked with document.getElementById. I have updated my question to show

Jon Taylor · Accepted Answer · 2012-07-14 11:50:32Z

1

Should you not be creating one object then json encoding the whole lot, rather than json encoding each row seperatley?

Im not great with sql but having a LIMIT greater than 1 means ur expecting more than one row back (potentially), correct?

If so then you are essentially returning multiple json responses in one response which I dont think you should be doing.

edited Jul 14, 2012 at 11:50

answered Jul 14, 2012 at 11:44

Jon Taylor

7,9255 gold badges32 silver badges55 bronze badges

4 Comments

Ben Over a year ago

thanks Jon. I didn't realise I was encoding each row separately and I am expecting more than one row back. how would you suggest?

Jon Taylor Over a year ago

I would suggest creating an array, then push each row onto the array. Once you have gone through every row then json encode the entire array. This way you will have one single json object containing all your rows.

Ben Over a year ago

jon, not to sound ungrateful, but I'm not a programmer, could you highlight/link a specific example I could work from. thanks

Jon Taylor Over a year ago

Theres an example in the other answer written by @Kyokasuigetsu

Collectives™ on Stack Overflow

AJAX PHP JSON encode not returning all results

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related