Page 1 of 1

UTF-8 problems with MySQL

Posted: Mon Feb 28, 2011 7:50 pm
by mecha_godzilla
Hi,

Very briefly, I seem to have a problem displaying UTF-8 characters retrieved from my MySQL database.

I have my shared hosting package with PHP set-up to use UTF-8 by declaring the following at the top of my scripts:

Code: Select all

ini_set('default_charset', 'UTF-8');
I also include the following line in my HTML template:

Code: Select all

<meta http-equiv="content-type" content="text/html; charset=utf-8" />
On the MySQL side of things, the collation is set to UTF-8 for both the database itself and individual fields. When I enter a special character (my test character is é) this displays correctly inside phpMyAdmin (which proves that MySQL is saving it properly) but when the same text is retrieved from my script I get the question mark symbol; this leads me to conclude that the problem is obviously to do with my script :mrgreen:

Could anyone offer any advice please - am I declaring the right ini_set() values? Do I need to declare more? My multibyte extension settings are:

mbstring.detect_order no value no value
mbstring.encoding_translation Off Off
mbstring.func_overload 0 0
mbstring.http_input pass pass
mbstring.http_output pass pass
mbstring.internal_encoding no value no value
mbstring.language neutral neutral
mbstring.strict_detection Off Off
mbstring.substitute_character no value no value

Thanks,

M_G

Re: UTF-8 problems with MySQL

Posted: Mon Feb 28, 2011 8:53 pm
by Weirdan
With those setting mbstring is irrelevant here. What you most likely need to do is to set proper connection charset after connecting to the mysql server using 'SET NAMES utf8' (which is the short way to tell the server that the client will send statements in utf8 and expect results in utf8):

Code: Select all

mysql_connect('localhost', 'root', 'password');
mysql_query('SET NAMES "utf8"');
// continue with your queries

Re: UTF-8 problems with MySQL

Posted: Tue Mar 01, 2011 4:30 pm
by iluvphp
Hi,

You should set the webpage heading using php as you cannot rely on it in the head section of a webpage.

Rite before everything else put this:

Code: Select all

<?php
// set content header type to utf-8
header('Content-type: text/html; charset=utf-8');
?>
^^ Make sure there is no spaces or anything before it else you will get headers already sent errors.

Also to set the MySQL Charset use the following. I place it below everything that's in my database file and when it's included then on my webpage's i know it is set in my database file so then you don't need to specify it in your queries etc. (just read the php comment note)

Code: Select all

# Let PHP Inform MySQL that i am working with UTF-8 data.
# Note that this only works in PHP >= 5.2.3 and MySQL >= 5.0.7
mysql_set_charset("utf8");

Re: UTF-8 problems with MySQL

Posted: Tue Mar 01, 2011 6:20 pm
by mecha_godzilla
Thanks for the responses :D

Calling

SET NAMES 'utf8'

seems to have solved the problem. It seems a bit strange that even though my installs of PHP and MySQL are specifically set to use UTF-8 I still have to explicity declare this - where is it picking up the 'default' value from, exactly? :crazy:

I used the character encoding menu in Firefox to make sure that UTF-8 was being used (which it was) so I didn't need to manually set the header, though saying that I don't normally use header() except for redirections. When I used telnet to access a test page I got

Content-Type: text/html; charset=UTF-8

and this test page didn't include the

ini_set('default_charset', 'UTF-8');

code that I added to the other scripts, so PHP seems to be using the correct character set by default.

Thanks again for your help,

M_G

Re: UTF-8 problems with MySQL

Posted: Tue Mar 01, 2011 8:28 pm
by Eran
It seems a bit strange that even though my installs of PHP and MySQL are specifically set to use UTF-8 I still have to explicity declare this
For some reason MySQL tries to detect the client's encoding and it's almost never UTF-8. To avoid that, add the following to your my.cnf:
[syntax]character-set-server=utf8
skip-character-set-client-handshake[/syntax]

And then you can avoid running the SET NAMES query on every page.

Re: UTF-8 problems with MySQL

Posted: Wed Mar 02, 2011 3:12 pm
by mecha_godzilla
Thanks Eran - that sounds like a good idea. I can't do this with my current hosting arrangements (because I don't have shell access) but the hosting company might be willing to update their set-up if I ask them about this.

M_G