MySql query in php only returns the last result when encoded in json

Question

I have a query (mysql) in php which when encoded to json only return the last result of the query however I am sure that the query returns more than one result:

$counter=0;
$reply=array();
$result = mysqli_query($conn,$stringQuery);;
if ($result->num_rows > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        $reply["json"]=$row;
        $counter++;
    }

    echo json_encode($reply);
} else {
    echo json_encode("0 results");
}
$conn->close();

when I stringify the result in javascript it only return the last entry of the table, or single result:

$.ajax({
    type: 'POST',
    url: 'SearchQuery.php',
    data: {
        'json': cond
    },
    contentType: "application/x-www-form-urlencoded;charset=UTF-8",
    dataType: 'json',
    success: function(response) {
        alert(JSON.stringify(response["json"]));

    }
});

$result = mysqli_query($conn,$stringQuery);; Extra semicolon — Tushar
– Tushar, Commented May 26, 2015 at 13:51

Tushar · Accepted Answer · 2015-05-26 13:42:25Z

3

Try this:

while ($row = mysqli_fetch_assoc($result)) {
    $reply[] = $row;
    // ^^^^^^^^^^^^^

    $counter++;
}
echo json_encode($reply);

answered May 26, 2015 at 13:42

Tushar

87.4k21 gold badges164 silver badges182 bronze badges

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

3 Comments

user1281678 Over a year ago

if i do that and change the alert in javascript to: alert(JSON.stringify(response)); nothing happens, there is no alert; Somethings happens in the php, however no error is presented

Tushar Over a year ago

@user1281678 Try alert(JSON.stringify(response)); Because you are not sending data in json now

user1281678 Over a year ago

as I said before: if i do that and change the alert in javascript to: alert(JSON.stringify(response)); nothing happens, there is no alert; Somethings happens in the php, however no error is presented

Sohil Desai · Accepted Answer · 2015-05-26 14:14:31Z

1

Here

$reply["json"]=$row;

in this line your last fetch row in while loop overwrite existing result so it is showing only last row as a result.

You can try

$reply["json"][]=$row;

So at ajax in success function

alert(JSON.stringify(response["json"]));

you can have a data accessible via key name "json".

answered May 26, 2015 at 14:14

Sohil Desai

2,9845 gold badges26 silver badges36 bronze badges

1 Comment

Sohil Desai Over a year ago

change contentType: "application/json" in ajax call

Fabio · Accepted Answer · 2015-05-26 14:23:53Z

1

Try to change fetch_assoc() with fetch_array()

while($row=$result->fetch_array()){
       $reply[] = $row;
       $counter++;
}
echo json_encode($reply);

Or try:

while($row=$result->fetch_array()){
     $items.=<<<EOD
    {
"r": "{$row['yourfield']}",
},
EOD;
$counter++;
}

header('Content-type: application/json');
?>
{
    "response": [
        <?php echo $item;?>
    ]
}

edited May 26, 2015 at 14:23

answered May 26, 2015 at 13:57

Fabio

851 gold badge2 silver badges14 bronze badges

6 Comments

user1281678 Over a year ago

$counter=0; //$reply=array(); $result = mysqli_query($conn,$stringQuery); if ($result->num_rows > 0) { // output data of each row while($row = mysqli_fetch_assoc($result)) { $reply = array($counter => $row); //$reply[]=$row; $counter++; } echo json_encode($reply); } else { echo json_encode("0 results"); } $conn->close(); this should work but it doesn't

Fabio Over a year ago

where you have error??? in console.log???? Do you have changed the $row['yourfield'] with your fieldname???? Are you sure that EOD code is written correct without format???? check it because the code is correct!!! I use it in my function page.

user1281678 Over a year ago

sorry my mistake. I've tried it now and no error and in the alert() gives a response[], empty

user1281678 Over a year ago

this is the code I have put together with your else{ $counter=0; //$reply=array(); $result = mysqli_query($conn,$stringQuery); if ($result->num_rows > 0) { // output data of each row while($row=$result->fetch_array()){ $items.=<<<EOD { "r": "{$row['first_name']}", }, EOD; $counter++; } header('Content-type: application/json'); ?> { "response": [ <?php echo $item;?> ] } <?php } else { echo json_encode("0 results"); } $conn->close(); } ?> 500 error

Fabio Over a year ago

no you can't do that... before you have to insert the ELSE Code and in the response you write only { "response": [ <?php echo $item;?> ] } .... Now try this code without exceptions..

|

Collectives™ on Stack Overflow

MySql query in php only returns the last result when encoded in json

3 Answers 3

3 Comments

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related