PHP，Mysql avg while循环到JSON

Question

I am having an issue understanding the proper use for a while loop in PHP. I am trying to get a JSON output like this ....

{"ratings":[{"id":"1","rating":"4.5"}},{"id":"2","rating":"3"}}]}

My php looks like this ...

<?php
$db_name="ratings";
require_once ('db_Connect.php');
mysql_select_db("$db_name")or die("cannot select DB");

$sql = "SELECT id, AVG(rating) FROM ratings GROUP BY id"; 

$result = mysql_query($sql) or die(mysql_error());
$result = mysql_fetch_array($result);

echo json_encode($result);
mysql_close($con);
?>

What the above php produces is ...

{"0":"1","id":"1","1":"1.5","AVG(rating)":"1.5"}

however, there are more results that should be displayed. And as you can see, this is not the anticipated results as there are only one row shown. And the format is not as desired.

My question is, where have I gone wrong with my code?

Answer 1

To produce a json like you want, you need to rearrange the result on array and finally encode in json format, like this:

$arrayToFormat = array();
while($row = mysql_fetch_array($result, MYSQL_NUM)){
      $arrayToFormat['ratings'][] = array('id' => $row[0], 'rating' => $row[1]);
}
echo json_encode($arrayToFormat);