UTF-8 problems with MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

UTF-8 problems with MySQL

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: UTF-8 problems with MySQL

Post 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
iluvphp
Forum Newbie
Posts: 2
Joined: Tue Mar 01, 2011 4:25 pm

Re: UTF-8 problems with MySQL

Post 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");
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: UTF-8 problems with MySQL

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: UTF-8 problems with MySQL

Post 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.
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: UTF-8 problems with MySQL

Post 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
Post Reply