PHP, JSON, and MySQL

Question

What is the easiest way to retrieve data from the database, and convert the data to a JSON String?
Is there any kind of helper class? or am I supposed to loop through the columns in my dataset to create the JSON string?

sample code would be great

Answer 1

Make an array from mySQL, and json_encode() it

Answer 2

You can use the json_encode function to convert a native PHP array or stdClass object to it's corresponding JSON representation:

$result = $db->query('SOME QUERY');
$set = array();
if($result->num_rows) {
   while($row = $result->fetch_array()) {
      $set[] = $row;
   }
}
echo json_encode($set);

Answer 3

Yes, use PHP's functions to handle JSON encoding.

Here's an example I got from this SO question:

$sth = mysql_query("SELECT ...");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);

Answer 4

Have you seen the json_encode() function? It takes an array as input and outputs JSON. So the most basic would be a two-dimensional array representing your table as input to json_encode

Answer 5

If you use the json_encode function then you're depending on somebody else's interpretation of the right way to format the json. Which means that you might come out with weird json, or have to contort your classes in evil ways to make the json come out right.

Just something to think about.