Getting data from MYSQL into JSON using PHP

Getting data from MYSQL into JSON using PHP

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

Stefan Bogdanescu

Founder & Senior Architect

Resolving Memory Exhaustion When Encoding Large MySQL Result Sets to JSON

When attempting to convert a raw MySQL result set into a JSON string using json_encode(), hitting a memory limit is a common stumbling block for developers working with larger datasets. The error message you are encountering—Allowed memory size of 33554432 bytes exhausted (tried to allocate 1979603 bytes)—is not necessarily a reflection of poor coding style, but rather a fundamental mismatch between how legacy PHP functions handle data and modern JSON requirements.

In the code snippet provided, the root cause lies in the interaction between mysql_fetch_row() and the subsequent array operations. The function mysql_fetch_row() returns a numeric array containing string values. While this might seem innocuous for small datasets, json_encode() works by iterating through the entire data structure to validate keys and serialize values. If your $test_data array contains hundreds or thousands of rows, the memory required to hold that massive associative/nested array in RAM before serialization can easily exceed standard limits, even when PHP.ini is configured with a generous 32MB allowance.

The immediate temptation is to increase the memory_limit directive further in php.ini. However, this is often a temporary fix that addresses symptoms rather than the underlying architectural issue. Increasing memory usage does not reduce the footprint of the data; it simply allows your script to crash later or slower. A more robust solution involves changing how you interact with the database and process the data stream.

The legacy mysql_* extension used in your example is deprecated and removed in newer PHP versions, but assuming you are running on an older environment for this specific task, the strategy to resolve memory exhaustion is to avoid loading the entire result set into memory as a single array. Instead, consider using mysql_fetch_array() or, preferably, mysql_fetch_assoc() combined with immediate serialization within the loop, or switching to PDO which offers better resource management.

However, if you must load all data into an array before encoding (perhaps for specific business logic requiring manipulation), the most efficient approach is to ensure the database query is optimized. If SELECT * returns a massive table without filtering, consider adding LIMIT clauses to process data in batches. You can fetch rows in chunks of 1,000, encode them individually into JSON fragments, and concatenate them with separators. This technique, known as chunking or pagination at the application level, ensures that no single array operation ever exceeds a manageable memory footprint.

For instance, instead of one massive json_encode() call on the entire dataset, you could iterate through the results in smaller loops:

$batchSize = 100;
$currentChunk = [];

while ($row = mysql_fetch_assoc($result)) {
    $currentChunk[] = $row;

    if (count($currentChunk) >= $batchSize) {
        $jsonFragment = json_encode(['data' => $currentChunk]);
        // Append to a file or stream, avoiding memory accumulation
        $output .= $jsonFragment . "\n";
        
        $currentChunk = []; // Reset the array
    }
}

// Handle remaining rows
if (!empty($currentChunk)) {
    $jsonFragment = json_encode(['data' => $currentChunk]);
    $output .= $jsonFragment;
}

By processing data in batches, you prevent the memory usage from scaling linearly with the dataset size. This approach respects the 32MB limit while still allowing you to generate valid JSON output. It is a critical trade-off: you sacrifice the ability to perform bulk operations on the entire dataset at once for the stability of not running out of memory.

If you are building new applications, consider migrating from the deprecated mysql_* functions to PDO or MySQLi. These extensions support prepared statements and often provide better control over result handling, such as using cursors in procedural mode (though less common now) or simply fetching specific columns rather than entire rows. Furthermore, modern frameworks like Laravel handle this abstraction elegantly, often returning collections that can be iterated efficiently without manual memory management.

Ultimately, the solution isn't just about tweaking PHP settings; it is about architecting your data retrieval to match your output requirements. If you don't need all 10,000 rows in a single JSON payload, don't fetch them all at once.

Our network