xiimoss xiimoss - 2 months ago 9
JSON Question

Return MYSQL query result via JSON array/object?

I have a table that contains a list of countries, i'm selecting all the countries from the table using

$query = $conn->query("SELECT * FROM countries");


I now need to turn this result into a JSON array/object for use in my android application, however i can only get it to return the last country from the table.

How would i go about turning the entire result/list of countries into JSON to be used in my application?

$response = array();

while($row = mysqli_fetch_assoc($query))
{

$response["country"] = $row;

}



echo json_encode ( $response );

Answer

In case you or a future reader is still not sure why what you were doing wasn't working, when you use the following code in your while loop:

$response["country"] = $row;

in the first iteration of the loop, the $response array is created, with one key ("country"), and the value of $row is assigned to that key. With each subsequent iteration, the "country" key is overwritten with the new value of $row. That's why you end up with only the last country.

When you change it to

$response["country"][] = $row;

as a couple of people suggested, you assign an array to the "country" key and append the value of $row to it, and with each subsequent iteration, the new value of $row is appended to that array, so at the end of the loop, you'll have all of the countries.

The fetchAll() method is a shortcut that precludes the need for fetching rows in a loop. It does come with some caveats that you can read about in the documentation, but if your result set is not too large and you're just going to json_encode and output it directly as you're doing here, it is a logical choice.