json_encode is returning NULL?

Question

For some reason the item "description" returns NULL with the following code:

<?php
include('db.php');

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

Here is the schema for my database:

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext COLLATE utf8_unicode_ci,
  `description` longtext COLLATE utf8_unicode_ci,
  `icon` longtext COLLATE utf8_unicode_ci,
  `date` longtext COLLATE utf8_unicode_ci,
  `company` longtext COLLATE utf8_unicode_ci,
  `companyurl` longtext COLLATE utf8_unicode_ci,
  `appurl` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Here is what is echoed out on the page:

[{"id":"4","name":"Noter 2","description":null,"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":null,"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"}]

Any ideas?

Answer 1

I bet you are retrieving data in non-utf8 encoding: try to put mysql_query('SET CHARACTER SET utf8') before your SELECT query.

Answer 2

If you have at least PHP 5.5, you can use json_last_error_msg(), which will return a string describing the problem.

If you don't have 5.5, but are on/above 5.3, you can use json_last_error() to see what the problem is.

It will return an integer, that you can use to identify the problem in the function's documentation. Currently (2012.01.19), the identifiers are:

0 = JSON_ERROR_NONE
1 = JSON_ERROR_DEPTH
2 = JSON_ERROR_STATE_MISMATCH
3 = JSON_ERROR_CTRL_CHAR
4 = JSON_ERROR_SYNTAX
5 = JSON_ERROR_UTF8

These can change in future versions, so it's better to consult the manual.

If you are below 5.3, you are out of luck, there is no way to ask what the error was.

Answer 3

ntd's anwser didn't solve my problem. For those in same situation, here is how I finally handled this error: Just utf8_encode each of your results.

while($row = mysql_fetch_assoc($result)){
    $rows[] = array_map('utf8_encode', $row);
}

Hope it helps!

Answer 4

few day ago I have the SAME problem with 1 table.

Firstly try:

echo json_encode($rows);
echo json_last_error();  // returns 5 ?

If last line returns 5, problem is with your data. I know, your tables are in UTF-8, but not entered data. For example the input was in txt file, but created on Win machine with stupid encoding (in my case Win-1250 = CP1250) and this data has been entered into the DB.

Solution? Look for new data (excel, web page), edit source txt file via PSPad (or whatever else), change encoding to UTF-8, delete all rows and now put data from original. Save. Enter into DB.

You can also only change encoding to utf-8 and then change all rows manually (give cols with special chars - desc, ...). Good for slaves...

Answer 5

You should pass utf8 encoded string in json_encode. You can use utf8_encode and array_map() function like below:

<?php
    $encoded_rows = array_map('utf8_encode', $rows);
    echo json_encode($encoded_rows);
?>

Answer 6

For anyone using PDO, the solution is similar to ntd's answer.

From the PHP PDO::__construct page, as a comment from the user Kiipa at live dot com:

To get UTF-8 charset you can specify that in the DSN.

$link = new PDO("mysql:host=localhost;dbname=DB;charset=UTF8");

Answer 7

AHHH!!! This looks so wrong it hurts my head. Try something more like this...

<?php
include('db.php');

$result = mysql_query('SELECT `id`, `name`, `description`, `icon` FROM `staff` ORDER BY `id` DESC LIMIT 20') or die(mysql_error());
$rows = array();
while($row = mysql_fetch_assoc($result)){
    $rows[] = $row;
}

echo json_encode($rows);
?>

• When iterating over mysql_num_rows you should use < not <=. You should also cache this value (save it to a variable) instead of having it re-count every loop. Who knows what it's doing under the hood... (might be efficient, I'm not really sure)
• You don't need to copy out each value explicitly like that... you're just making this harder on yourself. If the query is returning more values than you've listed there, list only the ones you want in your SQL.
• mysql_fetch_array returns the values both by key and by int. You not using the indices, so don't fetch em.

If this really is a problem with json_encode, then might I suggest replacing the body of the loop with something like

$rows[] = array_map('htmlentities',$row);

Perhpas there are some special chars in there that are mucking things up...

Answer 8

The PHP.net recommended way of setting the charset is now this:

mysqli_set_charset('utf8')

Answer 9

For me, an issue where json_encode would return null encoding of an entity was because my jsonSerialize implementation fetched entire objects for related entities; I solved the issue by making sure that I fetched the ID of the related/associated entity and called ->toArray() when there were more than one entity associated with the object to be json serialized. Note, I'm speaking about cases where one implements JsonSerializable on entities.