Wednesday, January 23, 2008

Enable UTF-8 on PHP, MySQL and Apache

Before we begin, you need r00t access to key Apache, PHP and MySQL configuration files.

Let’s start with the apache config. The location paths may differ based on your server setup. Look around and be sure that you are editing real config files and not the templates.

Apache Config - /etc/httpd/conf/httpd.conf

AddDefaultCharset UTF-8

PHP Config – /etc/php.ini

default_charset = "utf-8"

MySQL Config - /etc/my.cnf

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-character-set=utf8
default-collation=utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-client = utf8

Restart the above services once these updates have been applied.

Confirm if UTF-8 is Enabled:

# mysql –uroot –hlocalhost –p
# show variables like 'c%'

The above output should be:

+--------------------------+-------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| collation_connection | utf8_general_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
+--------------------------+-------------------------------------------------------------------+

It is possible that after these updates, PHP will continue to decode a UTF-8 character set in the form of question marks. Eg: ????

The solution is to call mysql_query() immediately after mysql_connect() has attempted a connection to the database.

$db_ = @mysql_connect (HOST, USER, PASSWORD, TRUE) or die("Could not connect");
mysql_query('SET NAMES utf8');
mysql_select_db(DB, $db_);

(Note the mysql_query('SET NAMES utf8'); above)

// Other db connect info here

// To be continued
Howto override php.ini through htaccess


6 comments:

Anonymous said...

Simple and right. Thank you

Jonas said...

Thanks, good article - another way of doing this (hint on your next step)
For me "AddDefaultCharset UTF-8" (wiuthout "s) was all I needed in the .htaccess file.

sadesh said...

How do I do this? I did all the modifications in the server side. Still "?" in place of accents.

The solution is to call mysql_query() immediately after mysql_connect() has attempted a connection to the database.

$db_ = @mysql_connect (HOST, USER, PASSWORD, TRUE) or die("Could not connect");
mysql_query('SET NAMES utf8');
mysql_select_db(DB, $db_);

(Note the mysql_query('SET NAMES utf8'); above)

Ruggero said...

Thanks, I have FINALLY able to make UTF-8 work form me.
I still had though to exceute these two:

$cmd = mysql_query ('SET NAMES "utf8"') or die('Could not query: ' . mysql_error());

$cmd = mysql_query ('SET CHARACTER SET "utf8"') or die('Could not query: ' . mysql_error());

Bartek said...

strangely, the above my.cnf conf did not work for me until i converted hyphens to underscores which results in

character_set_server=utf8
default_character_set=utf8
default_collation=utf8_unicode_ci
init_connect='SET NAMES utf8'
character_set_client = utf8

ubuntu-11.10/mysql-5.1.61

jobo49 said...

Excellent article - many thanks.

Your recommendations solved most of my issues, especially the my.ini changes.

I was left with a few issues (bad displays of accented characters)
- css syntax errors - I chased these by using the Firefox web developer console
- php string conversions - I replaced, for example, strtolower by mb_strtolower