Feature needed? Json_encode() fails silently
Feature needed? Json_encode() fails silently
I just finished having a bout with json_encode() while encoding a result set emanating from MySql. Json_encode() fails silently causing the result set to be returned as int 0. The issue in this case came from a record added or updated by a rich text client containing a single quote character.
I searched the forums and got a few hits on this exact same issue but with nearly same results I had. Turns out that the fix was well described in the solution offered in this thread: http://stackoverflow.com/a/9099949. However, mysql_set_charset("utf8") is deprecated and PHP5.0+ PDO uses the charset parameter in the PDO connection string.
Not a new problem apparently. Some folks also see "?" where a curly quote should exist (this problem was reported in these forums as well).
There are several ways of around this issue on the server side of the editor. The one I chose was to implement a new passed in parameter for charset=. Not perfect because of the abstraction offered by the database layer that comes with the editor's library but it certainly works well.
Could this become a possible enhancement?
I searched the forums and got a few hits on this exact same issue but with nearly same results I had. Turns out that the fix was well described in the solution offered in this thread: http://stackoverflow.com/a/9099949. However, mysql_set_charset("utf8") is deprecated and PHP5.0+ PDO uses the charset parameter in the PDO connection string.
Not a new problem apparently. Some folks also see "?" where a curly quote should exist (this problem was reported in these forums as well).
There are several ways of around this issue on the server side of the editor. The one I chose was to implement a new passed in parameter for charset=. Not perfect because of the abstraction offered by the database layer that comes with the editor's library but it certainly works well.
Could this become a possible enhancement?
This discussion has been closed.
Replies
The `sql()` method docs give an example with a possible workaround: https://editor.datatables.net/docs/current/php/class-DataTables.Database.html#_sql , but adding the character set to the DSN sounds like a good idea. It looks like it is specific to MySQL, but I'll add an option to the Editor driver for that.
Allan
Hello. I had a similar problem, and spent a few hours struggling to understand why I was getting an error for invalid JSON. Of course, I finally looked through a few thousand rows in MySQL and also found that there were some unicode characters for registered copyright (R). I simply changed these to HTML characters, ie
®, and the problem was resolved, valid JSON. As others might encounter the same problem, I thought I would note this here.To follow-up on my last note. I encountered the same problem again on another table, so after more poking around and re-reading about PDO, the solution I found for MySQL is as follows:
In the database directory (extensions/Editor/php/Database/Driver/Mysql) in the file Query.php, on line 45 is the following:
mysql:host={$host};{$port}dbname={$db}",I changed this to:
mysql:host={$host};{$port}dbname={$db};charset=utf8",and it worked perfectly. No more JSON errors caused by UTF/Unicode characters.
Good to hear! I might add an option for that by default as it has tripped us up a few times...
Allan