json_encode is returning NULL?

Asked 28/12, 2009 at 23:5 Answered 13/9, 2018 at 20:8

120

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?

Top answered 28/12, 2009 at 23:5 Comment(10)

Let's put the array keys in quotes, first of all. – Myelencephalon 28/12, 2009 at 23:8

Could you provide information on the schema of your "staff" table. Is there a column called description? – Koger 28/12, 2009 at 23:10

all of these fields will echo out if i just simply do an echo of $r['description'] outside of the for() statement? – Top 28/12, 2009 at 23:12

Or perhaps some example content from $r['description'] would help. What datatype is it? – Koger 28/12, 2009 at 23:13

could you make a screenshot of the database shema? ;-) – Dissension 28/12, 2009 at 23:13

did you tryed an $r = mysql_fetch_array($result); print_r($r); – Dissension 28/12, 2009 at 23:20

@Top but i didnt see any screenshot of the database shema, there is just text :-) no joking beside did you tryed the above $r = mysql_fetch_array($result); print_r($r); and post the result as a 3 mb printscreen, – Dissension 28/12, 2009 at 23:22

Added some more code ^^ here is screenshot: tarnfeldweb.com/stackoverflow.png – Top 28/12, 2009 at 23:26

Use JSON_PARTIAL_OUTPUT_ON_ERROR option to see the problem (eg. the field with UTF8 will be null). – Crowded 29/5, 2016 at 18:31

I had the same issue. It turns out that the data saved in the database has been badly encoded. French character like é,ë,' etc were translated in strange character such Ã, Ã, ¢, â‚ €. To solve the problem, make sure you clean up these strange characters. It worked for me! – Cellist 4/3, 2019 at 17:58

260

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

Zoography answered 29/12, 2009 at 0:36 Comment(9)

hi, this answer saved my life, thank you. I was having the same problem here. I had values with non-utf8 chars like "Validação de Formulários". I know this question is a little bit old now, but that's the awesomeness of internet!! – Aerometer 11/2, 2011 at 23:23

mysql_set_charset is better for the security reason since PHP 5.2.3. See php.net/manual/en/function.mysql-set-charset.php for the details. – Lisp 3/6, 2013 at 4:7

It is what scares me in programming! I have a schema in CHARSET Latin1 and COLLATION latin1_swedish_ci ... Then Why should I set my CHARSET to utf8? I really do not know why, but I know if I don't do this way, nothing is returned. Nice but obscure! – Argal 1/11, 2013 at 14:34

Because UTF8 is the lingua franca on the web. Instead of cluttering the API with additional parameters and overhead, PHP (rigthly) uses the most common encoding, leaving to you the conversion burden if you use an uncommon (or an almost dead, as in your case) encoding. – Zoography 1/11, 2013 at 15:51

The recommended way of doing this has now changed. I tried to edit this answer to include a link but was rejected. The correct way is in my answer below. – Gaultheria 19/9, 2014 at 4:38

I also had this, ended up changing the file encoding to utf-8 as the json result wasn't coming from a database query – Omalley 9/5, 2016 at 9:13

As suggested above, mysql_set_charset would be a better alternative: $conn = mysql_connect('host', 'user', 'password'); mysql_set_charset('utf8', $conn); – Disarray 19/12, 2017 at 9:25

This solved my problem but all my varchar fields have collation set to 'utf8_bin'. Is there something that can be done to the database itself to avoid running into issues like this? – Sylph 21/12, 2017 at 7:44

@VeeK it is not enough to have your fields stored in UTF-8: you must configure your server to answer to clients in UTF-8. AFAIK stock mysql and mariadb use latin1. – Zoography 31/12, 2017 at 9:50

120

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.

Czarina answered 29/12, 2009 at 1:25 Comment(0)

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!

Grider answered 13/3, 2013 at 14:59 Comment(1)

I had encoding problems too. w/ mixed encoding. solution i found: https://mcmap.net/q/144260/-remove-non-utf8-characters-from-string – Shantae 8/5, 2013 at 7:25

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

Neurasthenic answered 7/11, 2012 at 13:56 Comment(0)

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...

Conidiophore answered 22/2, 2013 at 18:22 Comment(1)

or use JSON_PARTIAL_OUTPUT_ON_ERROR option to see the problem (eg. the field with UTF8 will be null). – Crowded 29/5, 2016 at 18:32

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

Openmouthed answered 1/12, 2016 at 5:44 Comment(0)

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...

Greatly answered 28/12, 2009 at 23:19 Comment(9)

[{"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"}] – Top 28/12, 2009 at 23:21

@tarnfield: Well, is that what you want or not? Oh..you've got some additional info in there...here... let me fix that for you. – Greatly 28/12, 2009 at 23:25

yeah "description" returns null – Top 28/12, 2009 at 23:27

If the description is returning null then it probably is null. Try echo $row['description'].'<br/>'; in that loop and see what it says. – Greatly 28/12, 2009 at 23:28

if i do that, it echoes our the descriptions fine :) – Top 28/12, 2009 at 23:34

Amended question again...try that. – Greatly 28/12, 2009 at 23:35

Well, then you've baffled me. Play with some of the json_encode options: JSON_HEX_QUOT, JSON_HEX_TAG, JSON_HEX_AMP, JSON_HEX_APOS, JSON_FORCE_OBJECT ca.php.net/manual/en/function.json-encode.php – Greatly 28/12, 2009 at 23:43

The options parameter was added in PHP 5.3; you must be using an older version. Sorry dude ;) I give up. – Greatly 29/12, 2009 at 0:9

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

mysqli_set_charset('utf8')

Gaultheria answered 19/9, 2014 at 4:37 Comment(0)

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.

Curcio answered 13/9, 2018 at 20:8 Comment(0)

-4

I had the same problem and the solution was to use my own function instead of json_encode()

echo '["' . implode('","', $row) . '"]';

Pounds answered 31/1, 2011 at 12:52 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags