JSON, MySQL, PHP - encoding problem

Question

Hello guys.

I have been trying to implement an AJAX call on a database returning a JSON file with PHP and eventually met the encoding problem.

My database has some char fields with customers' names and, since it is in portuguese, european characters are pretty common. Hence the some results with those characters are returning null via JSON.

I have changed all charset settings (database, html, scripts) to UTF-8 but it is still a no go.

This is the function I use to retrieve the data:

function getJSON($sql){

 $rows = array();
  while($r = mysql_fetch_assoc($sql)) {
    $rows[] = $r;
 }

 print json_encode($rows);

}

And this is my DB table structure:

conta int(10),
senha char(40),
nome varchar(30),
sobrenome varchar(25),
mail varchar(30),
saldo decimal(65,2),
agencia varchar(30),
tipo int(1),
ativa int(1),
padrao int(1) 

Rows "nome" and "sobrenome" are those which primarily might contain european characters (~, ç, ´, `, etc.).

I have sought to find a function to convert the whole result array into utf8 but could not find it.

I wonder if you can bring me some light upon this problem.

Thanks.

Answer 1

It's been awhile but if you changed the database and or table from latin to utf8, it's possible the table columns still use a latin collation. Try SHOW FULL COLUMNS IN my_database.table; as a mysql console query and check to make sure the 3rd column (collation field) is utf8.

Answer 2

Try using MySQL's Convert function in your select statement.

Answer 3

You need to set the charset in many places.

Try the following:

Send a http header with this line in your php-script:

header("Content-Type: text/javascript; charset=utf-8");

You need to place this before you print the json response.

If that doesn't work, check that the connection-charset to mysql is set to utf-8 (Mysql has separate charset for database and for connections to it, and will convert on-the-fly if the don't match)