MySQL Collation/Fonts/Foregin Language Issues
Force Database Dump Restore To UTF-8:
mysql --default-character-set=utf8 database < database.sql
Jumbled Characters or Question Marks For Non-Latin Encoding:
This is a common problem for our international users. They use all kinds of languages which often do not display right when imported from lets say tis620 (thai) into utf8. So this is a step by step guide in getting their site to work:
1. Usually they have already created a database and imported incorrectly. Get them to provide you a backup for their original database off their previous host.
2. Delete all the tables from the database if they have one setup via phpMyAdmin.
3. Now in the database via phpMyAdmin go to operations and change the collation to the proper one, lets say its this620_thai_ci.
4. After having done that you should click the Import tab in phpMyAdmin and make sure you pick the right collation in this case it’s tis620.
5. The database should now be perfect.
6. Now comes the tricky part, you will generally have to make their php scripts run the proper collation before the queries, for custom scripts this will be their own problem usually as the scripts will be too sloppy to be easy to edit. But with non-custom scripts there is usually a class which uses only one or two calls in the entire script to make all the queries.
7. To find this function you should run a recursive grep:
cd /home/username/public_html/ grep -R -i 'mysql_query' *
Usually you will get results like database.php for quality scripts.
8. Most quality scripts will give you one or two results, you should then open up those files with your favorite editor and figgure out how the calls are made and have the script run these two queries before the real queries:
SET NAMES 'tis620' SET CHARACTER SET tis620_thai_ci
This will often look something like this:
//edited
$this->_cursor = mysql_query("SET NAMES 'tis620'",$this->_resource);
$this->_cursor = mysql_query("SET CHARACTER SET tis620_thai_ci",$this->_resource);
//end of edited, below is actual global mysql query in the file
$this->;_cursor = mysql_query( $this->_sql, $this->_resource );
Another example:
$this->query_id = mysql_query("SET NAMES 'cp1251'", $this->connection_id);
$this->query_id = mysql_query("SET CHARACTER SET cp1251_general_ci", $this->connection_id);
Everything should now start working and the ??? will go away!
