如何从mysqli结果中构建正确的json？

Question

I am trying to build a json object from my mysqli result. How do I go about it. At the moment it does not create a json looking object.

Here is my code:

$result = $dataConnection->prepare("SELECT id, artist, COUNT(artist) AS cnt FROM {$databasePrefix}users GROUP BY artist ORDER BY cnt DESC LIMIT 0 , 30");
$result->execute();
if($result->error)
{
die("That didn't work. I get this: " . $result->error);
}
$result->bind_result($id, $artist, $count);
$data = array();
while($result->fetch()){
$data[] = '{ id :'.$id.', artist :'.$artist.', count :'.$count.'}';
}
echo json_encode($data);
$dataConnection->close();

I want a data object like:

{"id":"27","artist":"myArtist","count":"29"},....

Answer 1

Don't build your json for the values array that you will call json_encode on

instead of:

$data[] = '{ id :'.$id.', artist :'.$artist.', count :'.$count.'}';

do

$data[] = array("id"=>$id, "artist"=>$artist, "count"=>$count);