convert mysqli result to json

Question

Hey everyone,

I have a mysqli query which i need to format as json for a mobile application.

I have managed to produce an xml document for the query results, however i am looking for something more lightweight. (see below for my current xml code)

Any help or info greatly appreciated people!

$mysql = new mysqli(DB_SERVER,DB_USER,DB_PASSWORD,DB_NAME) or die('There was a problem connecting to the database');

$stmt = $mysql->prepare('SELECT DISTINCT title FROM sections ORDER BY title ASC');
$stmt->execute();
$stmt->bind_result($title);

// create xml format
$doc = new DomDocument('1.0');

// create root node
$root = $doc->createElement('xml');
$root = $doc->appendChild($root);

// add node for each row
while($row = $stmt->fetch()) : 

     $occ = $doc->createElement('data');  
     $occ = $root->appendChild($occ);  

     $child = $doc->createElement('section');  
     $child = $occ->appendChild($child);  
     $value = $doc->createTextNode($title);  
     $value = $child->appendChild($value);  

     endwhile;

$xml_string = $doc->saveXML();  

header('Content-Type: application/xml; charset=ISO-8859-1');

// output xml jQuery ready

echo $xml_string;

Answer 1

Something like json_encode() ?

Answer 2

As mentioned, json_encode will help you. The easiest way is to fetch your results as you already do it and build up an array that can be passed to json_encode.

Example:

$json = array();
while($row = $stmt->fetch()){
  $json[]['foo'] = "your content  here";
  $json[]['bar'] = "more database results";
}
echo json_encode($json);

Your $json will be a regular array with each element in it's own index.

There should be very little changed in your above code, alternativly, you can return both XML and JSON since most of the code is the same.