How can I build a correct json from mysqli result?

问题:

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"},....

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

回答2:

$result = $dataConnection->query("SELECT id, artist, COUNT(artist) AS count FROM {$databasePrefix}users GROUP BY artist ORDER BY cnt DESC LIMIT 0 , 30");
$data = array();
while($row = $result->fetch_assoc()){
    $data[] = $row;
}
echo json_encode($data);

To tell you truth, mysqli is awful API to be used right in the application code.

Do yourself a favor and use at least PDO

$result = $dataConnection->prepare("SELECT id, artist, COUNT(artist) AS count FROM {$databasePrefix}users GROUP BY artist ORDER BY cnt DESC LIMIT 0 , 30");
$result->execute();
echo json_encode($result->fetchAll());

unlike mysqli, it's methods always works.

回答3:

If you use mysqli here is an example. I use it in combination with a javascript ajax call.
The output looks like this: [{"field1":"23","field2":"abc"},{"field1":"24","field2":"xyz"}]

$mysqli = mysqli_connect('localhost','dbUser','dbPassword','dbName');

/* check connection */
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}

$query = "SELECT field FROM table LIMIT 10";

if ($result = mysqli_query($mysqli, $query)) {
  $out = array();

  while ($row = $result->fetch_assoc()) {
    $out[] = $row;
  }

  /* encode array as json and output it for the ajax script*/
  echo json_encode($out);

  /* free result set */
  mysqli_free_result($result);

  /* close connection*/
  $mysqli->close();
}

/* close connection*/
$mysqli->close();

回答4:

just create an array of all your rows, then do:

echo json_encode($array)