Character Sets, MySQL and MODx

☆ A M B ☆
24,524 Posts

sottwell Reply #1, 20 years ago

I have an odd problem. I’ve got a MySQL database, version 4.1.14. I have a table, set to UTF8. I inserted data in German, the data came from a file in utf8. If I write a quick and dirty query to view the data, it comes out fine. But in a module I made for a site set to utf8 the "special" characters are garbage. I tried using utf8_decode, and they are fine. The problem is that I will be needing to support Chinese characters in this table, and they are trashed, and the utf8_decode function just results in question marks.

The thing that bothers me here is that this works just fine in a plain php file querying the database and displaying the results, including Chinese characters, but going through the module it scrambles it.

I have taken the exact same code from the plain php file and put it in the module. Same problem. The module fram/page is rendering utf8, but the data is displayed as 8859-1. If I change my page encoding to 8859-1, it scrambles it even more.

Any suggestions on solving this problem?

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

253 Posts

eastbind Reply #2, 20 years ago

Hi, Susan.

It is a very familiar problem to we Japanese.
If you have authority to edit my.cnf, solution is simple.
Just added following lines into [mysqld] in my.cnf.

character-set-server = utf8 
init-connect=SET NAMES utf8
collation-server = utf8_general_ci

After editing, you should restart MySQL, re-make the databases, tables, and import your data.

☆ A M B ☆
24,524 Posts

sottwell Reply #3, 20 years ago

Well, the database is set to DEC8. But the pages in the different languages display properly; the pages’ encoding is UTF8, and the text data charset is UTF8 just the same as the Manager frame . Why should this table behave differently?

Some of the records in the table were entered by different methods, from different sources, and seem to be fine. However, if I use the javascript inline editor and copy/paste the text from one field to another, the new text will be garbled.

However, doing the same thing on my server (mysql 5-something, the database is UTF8) works just fine, so I have to presume it’s their database. But if it messes up this data, why does it handle copy/pasting to the document editing fields with no trouble? Ah... because it’s getting copied from an 8859-1 source maybe?

I have a headache.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

253 Posts

eastbind Reply #4, 20 years ago

If you cannot change the character set of the database, and cannot change the my.cnf, I know only the following method.
1. Change the charset of tables of MODx from Default dec8 to utf8.
Export MODx tables to sql file with phpMyAdmin.
change the DEFAULT CHARSET to utf8 at all tables in the sql file
import the sql file.
2. Send "SET NAMES utf8" from MODx to MySQL just after connecting.

for back end. manager/index.php line 136-139

} else {
  + mysql_query("SET NAMES utf8;");
    mysql_select_db($dbase);
}

for front end. manager/include/extenders/dbapi.mysql.class.inc.php line 87-90

         exit;
      } else {
       + mysql_query("SET NAMES utf8;");
         $dbase = str_replace('`', '', $dbase); // remove the `` chars
         if (!@ mysql_select_db($dbase)) {

Do not you think this method to be troublesome than rewriting my.cnf?
If the database consists of utf8, it is simple.

12 Posts

nikolas Reply #5, 18 years ago

Thanks, this solution was helpful to me as well.

Web developer, Product Marketing Specialist and Automotive Journo