Multi-language applications and UTF-8 databases

AA-00334

By default, the databases on our servers are created with latin1_swedish_ci collation. This may cause some issues if you use your application with languages with non-standard characters (Cyrillic, Chinese, etc.).

Therefore, the best practice prior to installing your application is to modify your database to use UTF-8 collation first. This can be done through the phpMyAdmin tool in your cPanel. Once you have loaded it, you should select your database and click on the Operations tab on the top of the screen. Then select utf8_general_ci from the "Collation" drop-down menu and click on the "Go" button.

Next, you can proceed with installing your application, using the newly created database.

You can repeat the same steps for modifying the database of already installed applications. This, however, will change the collation only for the newly added data and may not be enough for your application to work properly. Therefore, you can create a PHP file (you can call it whatever you wish, e.g. collation.php) in your public_html folder with the following content:

<?php
$db = mysql_connect(‘localhost’,’USERNAME‘,’PASSWORD‘);
if(!$db)
echo "cannot connect to the database";
mysql_select_db(‘DATABASE‘);
$result=mysql_query(‘show tables’);
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}
}
?>

You should make sure you add the correct data into the USERNAME, PASSWORD and DATABASE variables. Next, just call the newly created file, and it will change the collation of all the existing fields to utf8_general_ci.

For most web applications this should be enough for them to start working correctly with the new encoding. However, some scripts may still malfunction. Unfortunately, in such cases you have to create a new database, change its collation and re-install your application with it.