Why does `json_encode()` return NULL for MySQL database fields, and how can I fix it?

JSON_ENCODE Returns NULL for Database Fields

While working with MySQL and PHP, you may encounter an issue where json_encode() returns NULL for fields in your database. Here's a specific example:

$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);

Possible Cause:

After investigating your database schema and the echoed output, it's likely that you're retrieving data in a non-UTF8 encoding.

Solution:

To resolve this issue, add the following line before your SELECT query:

mysql_query('SET CHARACTER SET utf8');

This command ensures that your MySQL connection uses UTF-8 encoding, which is required for json_encode() to properly handle UTF-8 characters in your database fields.

Updated Code:

mysql_query('SET CHARACTER SET utf8');
$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);

Expected Output:

Now, when you run the updated code, you should get the following output, where the "description" field contains non-NULL values:

[{"id":"4","name":"Noter 2","description":"Description of Noter 2","icon":"http:\/\/images.apple.com\/webapps\/productivity\/images\/noter2_20091223182720-thumb.jpg","date":"1262032317","company":"dBelement, LLC","companyurl":"http:\/\/dbelement.com\/","appurl":"http:\/\/noter2.dbelement.com"},{"id":"3","name":"Noter 2","description":"Description of Noter 2","icon":"http:\/\/images.apple.com\/webapps\/productivity\/images\/noter2_20091223182720-thumb.jpg","date":"1262032317","company":"dBelement, LLC","companyurl":"http:\/\/dbelement.com\/","appurl":"http:\/\/noter2.dbelement.com"}]

The above is the detailed content of Why does `json_encode()` return NULL for MySQL database fields, and how can I fix it?. For more information, please follow other related articles on the PHP Chinese website!