Hi all,
I am having big trouble with accents in MySQL databases from MODx.
The site I am building is powered by its own MySQL database (ie, other than the modx database), which is to be heavily edited both from the MODx Manager and from outside with mysql client programs. What prompts me to write this is the confusion of using MODx with UTF-8 and MySQL with latin1, which makes characters with accents appear well only in one of them - the one that was used to write the data. This makes it very difficult to be able to edit the database from mysql.
MODx is configured to use UTF8. When the MODx DBAPI writes into the database, it sends strings in UTF-8. Despite the database being in latin1, the data is stored in UTF8. So there is no problem in asking for that same data to display. It displays nicely in MODx.
However, if I try to see that data from MySQL, it displays bogus chars wherever a character with accent should appear:
mysql> select id, pagetitle from modx_site_content;
+----+----------------------------------+
| id | pagetitle |
+----+----------------------------------+
| 14 | Informações | // this should be "Informações"
| 3 | O que é um pouch sling |
| 5 | Segurança |
| 6 | Instruções |
OTOH, if I write data from within a MySQL client program, then it displays badly in MODx, with question marks on the place of accented chars.
I have ensured that the php mysql connection charset is latin1: mysql_client_encoding($db->conn) returns latin1.
I initially thought that this would be enough for the connection to convert outgoing data from UTF8 into latin1, and incoming data from latin1 into UTF8. However, this does not appear to be happening.
So I tried manually doing that using iconv(). Very nice to outgoing data (with INSERT) but very bad to incoming data (with SELECT), as I would have to parse every result, see if it is a string, and then do it. This makes the pages impossibly slow, with timeouts and everything.
Has anybody dealt with this problem before? Will I have to set MODx into ISO8159-*?
All the best,
João
☆ A M B ☆