Difference between revisions of "Change Database Collation"

Latest revision as of 12:00, February 10, 2009

Some languages require the Database collation to be changed in order to display characters correctly. You will find instructions here on how to change your database collation and configure AWBS to work correctly with this change.

Converting the Database

Copy and paste the example below into a new text file. Give this file a name of convertdb.php (or anything that you will remember, but with the .php extension)

Example:
<?php
$DIR=(dirname(__FILE__));
require($DIR."/../includes/dbconfig.php");
include($workdir."/includes/sessions.php");
include_once($workdir."/config.php");
$tables = mysql_list_tables($dbdatabase);
while (list($table) = mysql_fetch_row($tables)) {
sqlwritesingle("ALTER TABLE $table CONVERT TO CHARACTER SET latin5 COLLATE latin5_turkish_ci;");
echo "$table - DONE<br />";flush();ob_flush();
}
?>

Edit this line in the script to the correct character set and collation for your language:
sqlwritesingle("ALTER TABLE $table CONVERT TO CHARACTER SET latin5 COLLATE latin5_turkish_ci;");

Once satisfied with the changes. Save and upload the file to the tools folder of your AWBS Website.
At this point it is strongly suggested to backup your database.
To proceed with converting the database, open a web browser and open the url to your file you just uploaded.
For Example: http://www.yoursite.com/tools/convertdb.php
You should receive output on your screen informing you if the changes are successful.

mySQL Character Sets and Collation Table

Extended System Configuration

You will also need to make sure the following Extended System Configuration values are set:

DBCONFIG.PHP Modification

Make a backup of your /includes/dbconfig.php file.

Then edit your dbconfig file and look for the following lines:
function dbconnect(){
global $dbhost,$dbusername,$dbpassword,$dbdatabase;
$db_connect=@mysql_connect($dbhost,$dbusername,$dbpassword) or nosql();
@mysql_select_db($dbdatabase) or nosql();
return $db_connect;
}

Add the following line above return $db_connect;
mysql_query("SET NAMES 'latin5' COLLATE 'latin5_turkish_ci'");
Be sure to change latin5 and latin5_turkish_ci to the same as what you converted your database character set and collation to.

When you are done, that section should now look like the below example:
function dbconnect(){
global $dbhost,$dbusername,$dbpassword,$dbdatabase;
$db_connect=@mysql_connect($dbhost,$dbusername,$dbpassword) or nosql();
@mysql_select_db($dbdatabase) or nosql();
mysql_query("SET NAMES 'latin5' COLLATE 'latin5_turkish_ci'");
return $db_connect;
}