Encoding database data as JSON fails

Encoding database data as JSON fails

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

Stefan Bogdanescu

Founder & Senior Architect

Encoding database data as JSON fails

You might expect json_encode to simply serialize whatever PHP array you pass it, but sometimes the output reveals something hidden in the source data. In the specific case of a staff table where the description field is defined as longtext, running a query and encoding the result often produces null for that field, even if the database contains text there.

The core issue usually lies in how MySQL handles large text fields compared to how PHP expects them. The provided schema defines name, description, icon, date, company, companyurl, and appurl all as longtext. While this seems flexible, it introduces significant overhead when fetching data into memory.

When you execute the query:

$result = mysql_query('SELECT * FROM `staff` ORDER BY `id` DESC LIMIT 2') or die(mysql_error());
$rows = array();
while($row = mysql_fetch_assoc($result)){
    $rows[] = $row;
}
echo json_encode($rows);

you are pulling the entire contents of those columns into PHP variables. If the description field contains data, it should appear in the JSON output. However, if you see "description":null, it typically means one of two things: the column contains actual NULL values in the database, or there is a mismatch between what the client driver returns and what json_encode expects.

In older PHP versions using the deprecated mysql_* functions (as seen in your code), the behavior can be inconsistent with large text types. More critically, if your application logic or the specific MySQL client library truncates or mishandles very large strings, they might appear empty or nullified before encoding happens. But there is a more subtle possibility: the data type of the column versus the content.

If the description field in your database actually contains NULL values (which is allowed for longtext unless defined as NOT NULL), then json_encode will correctly serialize that as null. This isn't necessarily a bug in PHP; it's a faithful reflection of the database state. To verify this, you should run a direct SQL query to inspect the raw content:

SELECT id, description FROM staff ORDER BY id DESC LIMIT 2;

If the result set shows empty strings or NULL there, the issue is data integrity, not encoding logic. If the SQL client displays text but your PHP script sees null, you might be hitting a buffer limit or a specific driver quirk. The deprecated mysql_fetch_assoc() function sometimes struggles with very long fields depending on the MySQL client library version installed on the server.

A more robust approach involves updating to mysqli or PDO, which handle large text buffers more predictably. Here is an example using mysqli:

$mysqli = new mysqli("localhost", "user", "password", "dbname");
$result = $mysqli->query('SELECT * FROM `staff` ORDER BY `id` DESC LIMIT 2');

if ($result) {
    $rows = [];
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    echo json_encode($rows, JSON_PRETTY_PRINT);
} else {
    die("Query failed: " . $mysqli->error);
}

Using JSON_PRETTY_PRINT helps visually inspect the output to confirm if the field is truly empty or just formatted differently. Additionally, ensure that your PHP memory limit (memory_limit) in php.ini is sufficient to hold these longtext values in memory simultaneously. If the server runs out of memory during the fetch process, it might default to nulls or truncate data unexpectedly.

Another angle to consider is collation. Your schema uses utf8_unicode_ci. While this is standard, ensure that your connection charset matches this exactly. A mismatch can cause encoding issues where special characters in the description field get mangled or treated as errors by the JSON encoder.

Finally, if you are building an API for a modern application, relying on deprecated extensions like mysql_* is a liability. Migrating to PDO with prepared statements not only solves potential encoding ambiguities but also secures your application against SQL injection. The trade-off here is slightly more verbose code and the need to manage connection resources explicitly, but the stability gain is worth it.

If you are maintaining legacy code and cannot immediately migrate, at least verify the raw database content. If the description columns truly hold data, the problem likely resides in the PHP environment's buffer handling or an outdated MySQL client library that fails to parse longtext correctly when passed through fetch_assoc. Upgrading these components is the definitive fix.

Our network