Problems with mysql, php and utf8

eantoranz · 12-19-2008, 12:20 PM

Hi!

I want to show some data that I have on a DB.

When I connect with the terminal client, when I do a select, I get exactly the data I expected AND when I check with one sniffer, I see that the data that is being transfered on the wire is exactly that data in UTF8. Cool.

However, when I try to do the same from PHP, I just don't get the render some of the characters correctly. I have tried setting the client encoding with these (with different combinations and each one separate):

Code:

mysql_set_charset('utf8', $LINK);
mysql_query("SET NAMES 'utf8'", $LINK);
mysql_query("set character set 'utf8'", $LINK);
mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'", $LINK);

When I check with the sniffer, no matter what I do, I never get the same bytes on the wire (onr on the script result, by the way). For instance "ó" would be sent as 0xc3 0xb3 to the terminal (as expected), but when I do that on php, this is sent to the client instead: 0xc3 0x83 0xc2 0xb3

Any idea what's going on?

It's PHP5 on Ubuntu Intrepid.

graemef · 12-20-2008, 02:05 AM

I would guess that PHP is not handling the UTF8 conversion correctly. Check the documentation on mbstrings and change the ini file as directed by that chapter. You may get some success then.

eantoranz · 12-20-2008, 10:25 AM

I'll check... but then how come the data is coming _not_ in utf8 on the wire? That's not related to PHP, or is it?

lbharti · 08-04-2009, 06:45 PM

In fact, I also tested PHP5 with UTF-8 static data, and it displays alright after setting the header with charset utf-8, or setting the default charset to utf-8.

MySQL client displays the data just the way it is supposed to. I have enable three other options in my.cnf, but to no help.

init-connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci

Could you get it working? Please share the info if you did.

Thanks

graemef · 08-05-2009, 02:11 AM

What are the mbstring settings in php.ini

lbharti · 08-05-2009, 03:13 AM

Here are the values. I am using Ubuntu Hardy Heron.
mbstring
Multibyte Support enabled
Multibyte string engine libmbfl
HTTP input encoding translation enabled
Multibyte (japanese) regex support enabled
Multibyte regex (oniguruma) version 4.4.4
Multibyte regex (oniguruma) backtrack check On

Directive Local Value Master Value
mbstring.detect_order no value no value
mbstring.encoding_translation On On
mbstring.func_overload 0 0
mbstring.http_input pass pass
mbstring.http_output UTF-8 UTF-8
mbstring.internal_encoding UTF-8 UTF-8
mbstring.language neutral neutral
mbstring.strict_detection Off Off
mbstring.substitute_character no value no value

graemef · 08-05-2009, 04:23 AM

The key settings are (I believe)

mbstring.http_input UTF-8
mbstring.internal_encoding UTF-8
mbstring.http_output UTF-8
mbstring.detect_order auto

taken from a web server that I have running.

You may want to modify your settings to be in line with these and then check.

If that doesn't help then how are you getting the data off the database and then displaying it on the screen?

lbharti · 08-05-2009, 11:28 AM

Thanks for the prompt reply. I use mysql_connect to connect to the local database, the usual way. The data displays correct in the mysql terminal client, just PHP is either not receiving the correct data, or is messing it up. How do I trace this behavior?

eantoranz · 08-05-2009, 11:44 AM

Chck with one sniffer what's being transfered through the wire. Maybe you will see the same error I saw on the wire transfer.