OK, after having done some more research and testing, and letting myself forget about the problem for a while (it’s been Christmass, have you noticed
?) I came to what I think might be the solution.
I don’t have a deep knowledge of PHP internals, so it’s only my guessing based on documentation and testing (kind of a disclaimer here). The problem lies in the way PHP5 treats connections to the MySQL database as compared to PHP4. PHP4 respected the connection character set and collation as defined in the database. PHP5 doesn’t and instead use what’s compiled in the [tt]libmysql.dll[/tt] mentioned above - that is [tt]latin1[/tt].
Before I come to the solution, let’s do some testing.
First, check server configuration. Here is a dump of my MySQL 5.0.27 system variables as shown by phpMyAdmin 2.9.1.1:
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
character sets dir C:\(...)\mysql5\share\charsets\
collation connection utf8_general_ci
collation database utf8_general_ci
collation server utf8_general_ci
Now, compare it with a dump from the command line MySQL client:
mysql> show variables like 'character%';
+--------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\(...)\mysql5\share\charsets\ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)
mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
Can you see the difference? The command line client uses [tt]latin1[/tt] for the connection character set and [tt]latin1_swedish_ci[/tt] for the connection collation. Those are the default values compiled into [tt]libmysql.dll[/tt], which comes with PHP5, MySQL5 and is provided on dev.mysql.com site (see my previous post).
For comparison, below are the same data but from my MySQL 4.1.20 database (phpMyAdmin 2.9.1.1):
character set client utf8
character set connection utf8
character set database utf8
character set results utf8
character set server utf8
character set system utf8
character sets dir C:\(...)\mysql\share\charsets\
collation connection utf8_general_ci
collation database utf8_general_ci
collation server utf8_general_ci
And a dump from the command line client:
mysql> show variables like 'character%';
+--------------------------+--------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\(...)\mysql\share\charsets/ |
+--------------------------+--------------------------------------------+
7 rows in set (0.11 sec)
mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
As you can see, the [tt]libmysql.dll[/tt] provided with MySQL4 and built-in into PHP4 respects the server settings.
Now, what does all that mean?
Due to the [tt]libmysql.dll[/tt] PHP5 requires to have the connection character set and collation set per connection regardless of the database configuration. That means that the fact you have set everything to UTF-8 in your MySQL is not sufficient alone for PHP5 to connect using UTF-8. You have to set the character set and collation for the particular connection to the database! You do this by setting variables [tt]character_set_client, character_set_connection, character_set_results[/tt] to [tt]utf8[/tt] and [tt]collation_connection[/tt] to [tt]utf8_general_ci[/tt] for the connection you are using.
How to make MODx work in UTF-8 under PHP5 and MySQL5?
As I showed above, the connection to the database must use UTF-8. The shortest and simplest way is to use the command below once the connection to MySQL has been established:
<?php
mysql_query( 'SET NAMES utf8' );
?>
After searching in the MODx code and testing the effects I found two places, where it should be inserted:
<?php
# /manager/index.php on line 133 (revision 2106):
// connect to the database
if(@!$modxDBConn = mysql_connect($database_server, $database_user, $database_password)) {
die("<h2>Failed to create the database connection!</h2>. Please run the MODx <a href='../install'>install utility</a>");
} else {
# Sets connection encoding to UTF-8 for PHP5
mysql_query( 'set names utf8' );
mysql_select_db($dbase);
}
?>
This will take care of the back-end as well as strings contained in chunks. And the second one for front-end and category drop-downs in the manager:
<?php
# /manager/includes/extenders/dbapi.mysql.class.inc.php on line 85 (revision 2106):
if (!$this->conn = ($persist ? mysql_pconnect($host, $uid, $pwd) : mysql_connect($host, $uid, $pwd, true))) {
$modx->messageQuit("Failed to create the database connection!");
exit;
} else {
# Sets connection encoding to UTF-8 for PHP5
mysql_query( 'set names utf8' );
$dbase = str_replace('`', '', $dbase); // remove the `` chars
?>
As far as I have tested it works fine with Polish non-ASCII characters.
This is a dirty hack and it would require polishing to include the connection encoding for PHP5 and MySQL in MODx database configuration and this way provide an elegant solution.
I hope my findings will be helpful to someone.
PS. Of course you might want to try [tt]libmysql.dll[/tt] from PHP4 or MySQL4 and it would probably work. But its taken from MySQL3 (3.23?) and, for example, it doesn’t support the new authentication methods introduced in MySQL4. It is not advised to use older ones. But then, hey, it’s your choice. Good luck
Edit:
FS#734 — Set MySQL connection encoding to make non-ASCII characters work under PHP5
This happens in both front- and back-end (manager).
MODX Staff
}
I’m glad I could help.
☆ A M B ☆
