How to convert mysqli result to JSON?
Moving from XML to JSON for mobile applications often comes down to a single change in how you handle data retrieval. Your current implementation relies on DOMDocument and bind_result to manually construct an XML tree. While this works, it is significantly heavier than necessary for simple data transfers. The modern approach leverages the mysqli driver's built-in ability to serialize results directly into JSON strings.
The most efficient way to convert your query results to JSON without writing a single line of DOM manipulation code is to use the fetch_all() method combined with the MYSQLI_ASSOC flag. This tells the driver to fetch every row as an associative array and then encode that structure directly into a JSON string.
Here is how you can refactor your existing logic:
$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();
// Retrieve results as an associative array and encode to JSON
$json_string = json_encode($stmt->get_result()->fetch_all(MYSQLI_ASSOC));
header('Content-Type: application/json; charset=utf-8');
echo $json_string;
This approach eliminates the need for bind_result in this specific context. When using fetch_all(), you do not need to manually bind variables to individual columns before fetching. The driver handles the mapping internally, returning a nested array structure that json_encode can immediately process. The resulting JSON output will look like an array of objects, which is the standard format expected by most mobile frameworks.
Handling Complex Data Structures
One common pitfall when switching from XML to JSON is assuming all queries return single-column results. In your original code, you selected only title. If your query needs to return multiple columns, say id and content, the resulting JSON structure changes automatically.
$stmt = $mysql->prepare('SELECT id, title, content FROM sections ORDER BY id DESC');
$stmt->execute();
$json_string = json_encode(
$stmt->get_result()->fetch_all(MYSQLI_ASSOC),
JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE
);
header('Content-Type: application/json; charset=utf-8');
echo $json_string;
Notice the addition of JSON_PRETTY_PRINT. This flag is optional but highly useful during development. It formats the output with indentation and newlines, making it readable in a browser console or mobile debugger without altering the underlying data structure. For production environments where bandwidth matters, you can omit this flag to send a minified JSON string.
Why Not Use fetch_array()?
You might consider using fetch_array(MYSQLI_ASSOC) inside a while loop and manually pushing values into an array before encoding. While functionally similar, the one-liner using fetch_all() is generally preferred for its conciseness. However, there is a distinct trade-off regarding memory usage.
The fetch_all() method loads the entire result set into memory at once. If your query returns millions of rows from a large table like sections, this approach could trigger a memory exhaustion error or cause high CPU usage while serializing the massive JSON string. In such scenarios, you should stick to a loop:
$data = [];
while ($row = $stmt->get_result()->fetch_assoc()) {
$data[] = ['title' => $row['title']]; // Customize keys as needed
}
$json_string = json_encode($data);
This pattern allows you to process data incrementally or apply filtering logic before the final encoding step.
Character Set Considerations
Your original code used ISO-8859-1 for the XML header. JSON is typically encoded in UTF-8. Mobile applications expect UTF-8 strings by default. Therefore, ensure your database connection supports UTF-8 and update the HTTP header to reflect this:
header('Content-Type: application/json; charset=utf-8');
If you are using a framework like Laravel, you can also set the global JSON encoding options using App::setJSONOptions() if you need consistent behavior across your entire application. For raw PHP scripts, passing options directly to json_encode is the most explicit way to control behavior.
Finally, always verify that the mysqli connection was successful before attempting to execute queries or fetch results. The or die() clause in your connection string handles the initial failure, but it is good practice to check if $stmt is valid after preparation and if the result set is empty before trying to encode it.