Return MYSQL query result via JSON array/object?

Question

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

$response["country"] = $row; should be $response["country"][] = $row; — Noman
– Noman, Commented Sep 27, 2016 at 12:32
Good god. When will people finally start reading the documentation? php.net/manual/en/mysqli-result.fetch-all.php — arkascha
– arkascha, Commented Sep 27, 2016 at 12:33

segFault · Accepted Answer · 2016-09-27 12:35:33Z

2

You could use mysqli_fetch_all like

$response = array();
$response['country'] = mysqli_fetch_all($query, MYSQLI_ASSOC);
echo json_encode($response);

answered Sep 27, 2016 at 12:35

segFault

4,0541 gold badge21 silver badges34 bronze badges

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

Comments

Don't Panic · Accepted Answer · 2016-09-27 12:58:47Z

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.

Shahrukh · Accepted Answer · 2016-09-27 12:33:36Z

0

$response = array();

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

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

}



echo json_encode ( $response );

answered Sep 27, 2016 at 12:33

Shahrukh

1025 bronze badges

2 Comments

arkascha Over a year ago

Working, but inefficient.

abarisone Over a year ago

Please edit with more information. Code-only and "try this" answers are discouraged, because they contain no searchable content, and don't explain why someone should "try this".

Razib Al Mamun · Accepted Answer · 2016-09-27 14:30:50Z

0

If you can use $response["country"] = $row; all $row data every loop new initialize in $response variable. so if when you return $response array you can get last data.

So, Please follow bellow code

$response = array();
while($row = mysqli_fetch_assoc($query))
{
   $response["country"][] = $row;
}
echo json_encode ( $response );

if you thinks you not use while loop then you can also bellow code

$response = array();
$response['country'] = mysqli_fetch_all($query, MYSQLI_ASSOC);
echo json_encode($response);

answered Sep 27, 2016 at 14:30

Razib Al Mamun

2,7111 gold badge18 silver badges24 bronze badges

Collectives™ on Stack Overflow

Return MYSQL query result via JSON array/object?

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related