JSON encode MySQL results

JSON encode MySQL results

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

JSON encode MySQL results: The right way to handle query outputs

A common point of confusion when working with PHP and MySQL involves the json_encode() function. Developers often ask if they can simply pass a raw result set from a database query into this function and expect a valid JSON document, or if manual iteration is strictly required. The answer depends entirely on how your data was retrieved and what structure you need for the final output.

If you are using PDO or mysqli with default fetch modes (like PDO::FETCH_ASSOC), the $stmt->fetchAll() method returns an array of arrays. Each inner array represents a row, and keys correspond to column names. You can pass this entire multi-dimensional array directly to json_encode(). However, the output will be a JSON object where every field is itself an array of values. For example, if your table has columns id and name, the resulting JSON will look like {"id": [1, 2], "name": ["Alice", "Bob"]}. While technically valid JSON, this format is rarely useful for APIs or client-side rendering because it flattens row-level relationships into a single column of values.

To achieve a standard list of objects—where each item represents a complete row—you must iterate through the rows. You can do this by looping over the fetched array and passing individual items to json_encode(), though a more efficient approach is to let PHP handle the recursion automatically when converting the final structure. If you use PDO with PDO::FETCH_ASSOC and simply wrap your loop in a function that returns an array of objects, json_encode() can serialize the entire collection into a JSON array of objects:

$pdo = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->query("SELECT id, name FROM users");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// This produces a valid JSON array of objects
echo json_encode($rows, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

The key distinction lies in the fetch mode. If you need to manipulate each row—say, adding a formatted date or calculating an aggregate—you must iterate explicitly. Attempting to json_encode() a result set containing PDO::FETCH_BOTH (which includes numeric indices) can lead to unpredictable output if the client expects associative arrays only. Always ensure your fetch mode matches the expected JSON structure.

Performance is another consideration when dealing with large datasets. While json_encode() handles standard arrays efficiently, encoding massive result sets into a single string can strain memory usage, especially in long-running scripts or CLI tools. In high-traffic applications, consider streaming the output or chunking large queries if you are pushing data to a client that supports partial reads. However, for most web APIs returning lists of records, standard encoding remains performant enough.

There is also the matter of special characters and null values. json_encode() automatically escapes quotes and backslashes, which is desirable. You should generally use the JSON_UNESCAPED_UNICODE flag to avoid double-encoding UTF-8 sequences unless you have a specific requirement for escaped Unicode. Be aware that null in PHP becomes null in JSON, but an empty string remains "". This distinction can cause issues if your backend logic expects strict schema adherence on the client side.

If you are building a Laravel application, the ORM (Eloquent) handles fetching and accessing relationships differently than raw SQL queries. While the underlying mechanism still relies on PHP arrays for serialization, Eloquent models provide accessors that ensure attributes are formatted before being serialized. This abstraction layer reduces the need for manual iteration when constructing JSON responses, as the model's toArray() method already prepares the data structure correctly.

For complex transformations where you need to nest results or denormalize data before encoding, iterating is unavoidable. You might encounter a scenario where you fetch a user and their associated posts. Simply encoding the raw rows won't capture this relationship without manual mapping. In such cases, looping through the primary result set and fetching related data within the loop (or using eager loading) ensures the JSON structure reflects the intended hierarchy.

Understanding these nuances prevents fragile code that breaks when schema changes or data types shift. Always verify the output format against your API contract before deploying to production.

Our network