Search with utf8 characters in server side datatables with JSON-MySQL data source

AlejandroQ

Hi.

I hope this help someone.

The environment:
- Files encoded with utf-8. IDE, editors configured to save/edit files with utf8 as default
- MySQL database created with utf-8 encoding, collation, conections.
- Table fields with utf8 encoding (char, varchar, text fields)
- Apache web server on Linux with utf8 encoding (.htaccess)
- Meta tags in views / pages with utf-8 meta
- Datatables plugin version 1.8.1

As you can see, all is in utf8 encoding for correct display / save data with this encoding.

When you provide server side data in JSON format with PHP to dataTables plugin, function json_encode($response), PHP <= 5.3 encode the response like this case:

http://stackoverflow.com/questions/7381900/php-decoding-and-encoding-json-with-unicode-characters

In PHP 5.4 >=, in json_encode function, you can choose or skip this default function output. But in my case, production servers are with PHP 5.3.

This isn´t datatable error / bug; you need to clean your results before send to datatables when use json_encode with PHP 5.3 <=.

First use this PHP code (extracted from above article) to clean your json_encode($response) output and keep original utf8 chars in response:

[code]
function jsonRemoveUnicodeSequences($struct) {
$rep = preg_replace("/\\\\u([a-f0-9]{4})/e", "iconv('UCS-4LE','UTF-8',pack('V', hexdec('U$1')))", json_encode($struct));
return stripslashes($rep);
}
[/code]

Now it works. Output from server side to datatables is clean. But a little problem was given. When you search using datatables search box, and your database results are all in upper case, the search with utf8 chars only work if you type the chars in upper case.

A litte 'patch' helped me to keep the functionality when user type in upper or lower case:

In jquery.dataTables.js source file of DataTables, find this function:

[code]
function _fnFilter( oSettings, sInput, iForce, bRegex, bSmart )
{
var i;
var rpSearch = _fnFilterCreateSearch( sInput, bRegex, bSmart );
.......
[/code]

In function body first line, add this:

[code]
function _fnFilter( oSettings, sInput, iForce, bRegex, bSmart )
{
sInput = sInput.toUpperCase()
var i;
var rpSearch = _fnFilterCreateSearch( sInput, bRegex, bSmart );
.......
[/code]

And that's all! DataTables is amazing.