PHP MySQL using Latin1(iso-8859-1) despite UTF-8 settings

Question

Once again I have a weird and tricky problem.

I've been working with converting my MySQL databases (and everything else on my server for that matter) to UTF-8 to avoid having to convert text when getting and putting text into the different databases.

I think I've partially succeeded because SHOW VARIABLES LIKE 'character_set%' returns:

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

But still mysql_client_encoding($con); returns latin1 and in the output every special character is replaced with �. My conclusion is that the client or the connection between PHP and the MySQL database is using latin1 even though I've been specifying utf-8 in the document header and in my.ini with the following code:

character-set-server = utf8
character-set-client = utf8
default-character-set = utf8

edit: I've added the settings above under both [client], [mysqld] and [mysql]

When I use mysql_query('SET NAMES utf8;'); or mysql_set_charset("utf8"); the text shows properly but for me that's not a sollution, just a temporary fix.

Does anyone know how to force PHP (or whatever it is reverting to latin1) to use utf-8?

I should mention that I'm using Windows 2003 Server and Apache 2.

Gilbreath

I should point out that the mysql_xxx() functions are considered obsolete and should no longer be used. The PHP manual recommends using either the equivalent mysqli_xx() functions or the PDO library instead. – Advection

Answer 1

I've been working with converting my MySQL databases (and everything else on my server for that matter) to UTF-8 to avoid having to convert text when getting and putting text into the different databases.

You would still have to make sure the target database (or system) is setup to use UTF-8 by default - and the library you are using to connect to is is using the correct character set. Just because data is in UTF-8 doesn't make it universally compatible.

When I use mysql_query('SET NAMES utf8;'); or mysql_set_charset("utf8"); the text shows properly but for me that's not a sollution, just a temporary fix.

This is the proper solution. Since your connection's encoding determines how the data is received by your client (in this case, PHP).

To set it as default; you need to configure MySQL appropriately and then restart the server:

[client]
default-character-set=UTF8

Note that this affects all clients even the command line mysql client.

Closing

To set it as default; you need to configure MySQL appropriately and then restart the server. I feel like I've already completed this step. Do you mean that SET NAMES is the only other way to do it? It feels like there should be a simpler way than to use SET NAMES every time I have to fetch something from the database. – Gilbreath

You need to modify the [client] section. I have updated the question with the configuration variable you need from the charset configuration page from the manual. – Closing

I forgot to mention in my question that I've already added that line under client as well as under mysqld and mysql. It doesn't seem to work. – Gilbreath