PHP MySQL UTF8

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
tuga
Forum Newbie
Posts: 7
Joined: Mon Aug 25, 2008 9:08 am

PHP MySQL UTF8

Post by tuga »

I have this problem I'm building a site and I want to use the encoding of UTF-8, so in the header of my html pages I have

Code: Select all

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
I'm getting some data from a MySQL database that has the Character set utf8 (UTF-8 Unicode) and the Collation utf8_general_ci.

When I echo the data in the page I get this small squares filled up with letters( ? ) when there are some special characters like ç.

If I do something like

Code: Select all

echo htmlentities($row['str'], ENT_COMPAT, 'ISO-8859-1');
it produces the correct result.

What I would like is to understand why this happens if everything is set to utf-8 and why do I have to convert to latin1 to make it work.

Thanks
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP MySQL UTF8

Post by Eran »

Probably the database connection collation is ISO-8859 (for some reason, this is the default in many distributions). You can change in two ways - edit the mysql configuration file, or issue a names / charset query
http://dev.mysql.com/doc/refman/5.0/en/ ... ction.html
User avatar
jazz090
Forum Contributor
Posts: 176
Joined: Sun Apr 12, 2009 3:29 pm
Location: England

Re: PHP MySQL UTF8

Post by jazz090 »

all settings (character set and collation) in mysql need to be utf8_unicode_ci not general and certainly not latin. you have to ensure that both the submitting page and the printing page contain the meta tag header. if you upload to db by JUST mysql_real_escape_string and echo it back, it should work properly without htmlentities.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP MySQL UTF8

Post by Eran »

all settings (character set and collation) in mysql need to be utf8_unicode_ci not general
That's certainly not a requirement for using UTF. the difference between the various flavors of the same set (utf_*) is accuracy and speed of comparison and sorting. All of those can be used to store UTF content.
tuga
Forum Newbie
Posts: 7
Joined: Mon Aug 25, 2008 9:08 am

Re: PHP MySQL UTF8

Post by tuga »

pytrin wrote:Probably the database connection collation is ISO-8859 (for some reason, this is the default in many distributions). You can change in two ways - edit the mysql configuration file, or issue a names / charset query
http://dev.mysql.com/doc/refman/5.0/en/ ... ction.html
You are completely right!!!

I tried both solutions and everything works fine.

I have just installed the MySQL Server on my PC and on the Options I have set it to utf8, and there is actually a line in my.ini with this option
default-character-set=utf8
do you have any ideas why doesn't take that in consideration, and I have to add
init_connect= 'SET NAMES utf8'
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: PHP MySQL UTF8

Post by Eran »

A somewhat annoying and little documented feature of mysql is that the client can override the default charset. add the following directive to your mysql configuration file -

Code: Select all

skip-character-set-client-handshake
Post Reply