Difference between revisions of "Change Database Collation"

From AWBS Wiki
Jump to: navigation, search
(→‎Converting the Database)
(→‎DBCONFIG.PHP Modification)
 
(21 intermediate revisions by the same user not shown)
Line 3: Line 3:
 
== Converting the Database ==
 
== 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: <br />
 
<code>
 
<code>
−
<?php
+
<?php <br />
−
$DIR=(dirname(__FILE__));
+
$DIR=(dirname(__FILE__)); <br />
−
require($DIR."/../includes/dbconfig.php");
+
require($DIR."/../includes/dbconfig.php"); <br />
−
include($workdir."/includes/sessions.php");
+
include($workdir."/includes/sessions.php"); <br />
−
include_once($workdir."/config.php");
+
include_once($workdir."/config.php"); <br />
 +
$tables = mysql_list_tables($dbdatabase); <br />
 +
while (list($table) = mysql_fetch_row($tables)) { <br />
 +
sqlwritesingle("ALTER TABLE $table CONVERT TO CHARACTER SET latin5 COLLATE latin5_turkish_ci;"); <br />
 +
echo "$table - DONE<nowiki><br /></nowiki>";flush();ob_flush(); <br />
 +
} <br />
 +
?> <br />
 +
</code>
 +
<br />
 +
Edit this line in the script to the correct character set and collation for your language:<br />
 +
'''sqlwritesingle("ALTER TABLE $table CONVERT TO CHARACTER SET latin5 COLLATE latin5_turkish_ci;");'''<br />
 +
<br />
 +
Once satisfied with the changes.  Save and upload the file to the tools folder of your AWBS Website.<br />
 +
At this point it is '''strongly''' suggested to backup your database.<br />
 +
To proceed with converting the database, open a web browser and open the url to your file you just uploaded.<br />
 +
For Example: ''<nowiki>http://www.yoursite.com/tools/convertdb.php</nowiki>''<br />
 +
You should receive output on your screen informing you if the changes are successful.<br />
  
−
$tables = mysql_list_tables($dbdatabase);
+
=== mySQL Character Sets and Collation Table ===
  
−
while (list($table) = mysql_fetch_row($tables)) {
+
{| border = 1
−
  sqlwritesingle("ALTER TABLE $table CONVERT TO CHARACTER SET latin5 COLLATE latin5_turkish_ci;");
+
|- bgcolor=orange
−
  echo "$table - DONE<br />";flush();ob_flush();
+
! Charset !! Description !! Default collation
−
  }
+
|-
−
  ?>
+
| big5    || Big5 Traditional Chinese    || big5_chinese_ci   
−
</code>
+
|-
 +
| dec8    || DEC West European          || dec8_swedish_ci   
 +
|-
 +
| cp850    || DOS West European          || cp850_general_ci 
 +
|-
 +
| hp8      || HP West European            || hp8_english_ci   
 +
|-
 +
| koi8r    || KOI8-R Relcom Russian      || koi8r_general_ci 
 +
|-
 +
| latin1  || cp1252 West European        || latin1_swedish_ci  
 +
|-
 +
| latin2  || ISO 8859-2 Central European || latin2_general_ci 
 +
|-
 +
| swe7    || 7bit Swedish                || swe7_swedish_ci   
 +
|-
 +
| ascii    || US ASCII                    || ascii_general_ci 
 +
|-
 +
| ujis    || EUC-JP Japanese            || ujis_japanese_ci 
 +
|-
 +
| sjis    || Shift-JIS Japanese          || sjis_japanese_ci 
 +
|-
 +
| hebrew  || ISO 8859-8 Hebrew          || hebrew_general_ci 
 +
|-
 +
| tis620  || TIS620 Thai                || tis620_thai_ci   
 +
|-
 +
| euckr    || EUC-KR Korean              || euckr_korean_ci   
 +
|-
 +
| koi8u    || KOI8-U Ukrainian            || koi8u_general_ci 
 +
|-
 +
| gb2312  || GB2312 Simplified Chinese  || gb2312_chinese_ci 
 +
|-
 +
| greek    || ISO 8859-7 Greek            || greek_general_ci 
 +
|-
 +
| cp1250  || Windows Central European    || cp1250_general_ci 
 +
|-
 +
| gbk      || GBK Simplified Chinese      || gbk_chinese_ci   
 +
|-
 +
| latin5   || ISO 8859-9 Turkish          || latin5_turkish_ci
 +
|-
 +
| armscii8 || ARMSCII-8 Armenian          || armscii8_general_ci
 +
|-
 +
| utf8    || UTF-8 Unicode              || utf8_general_ci   
 +
|-
 +
| ucs2    || UCS-2 Unicode              || ucs2_general_ci   
 +
|-
 +
| cp866    || DOS Russian                || cp866_general_ci 
 +
|-
 +
| keybcs2 || DOS Kamenicky Czech-Slovak  || keybcs2_general_ci
 +
|-
 +
| macce    || Mac Central European        || macce_general_ci 
 +
|-
 +
| macroman || Mac West European          || macroman_general_ci
 +
|-
 +
| cp852    || DOS Central European        || cp852_general_ci 
 +
|-
 +
| latin7  || ISO 8859-13 Baltic          || latin7_general_ci  
 +
|-
 +
| cp1251  || Windows Cyrillic            || cp1251_general_ci  
 +
|-
 +
| cp1256  || Windows Arabic              || cp1256_general_ci 
 +
|-
 +
| cp1257  || Windows Baltic              || cp1257_general_ci 
 +
|-
 +
| binary  || Binary pseudo charset      || binary           
 +
|-
 +
| geostd8  || GEOSTD8 Georgian            || geostd8_general_ci
 +
|-
 +
| cp932    || SJIS for Windows Japanese  || cp932_japanese_ci 
 +
|-
 +
| eucjpms  || UJIS for Windows Japanese  || eucjpms_japanese_ci
 +
|}
  
 
== Extended System Configuration ==
 
== Extended System Configuration ==
 +
 +
You will also need to make sure the following Extended System Configuration values are set:<br />
 +
 +
{| border = 1
 +
|- bgcolor=orange
 +
! SETTING !! DESCRIPTION
 +
|-
 +
|'''admin_content_lang''' || Content language for admin area <br />Used for this Meta tag: <meta http-equiv="Content-Language" content="<?=$admin_content_lang?>">
 +
|-
 +
|'''admin_content_type''' || Content type for admin area. eg - ''charset=iso-8859-1'' <br />Used for this Meta Tag: <meta http-equiv="Content-Type" content="text/html; <?=$admin_content_type?>">
 +
|-
 +
|'''cfcharset''' || Character set override for contact form ''(Set the charset to use)''
 +
|-
 +
|'''content_lang''' || Content language for admin area
 +
|}
  
 
== DBCONFIG.PHP Modification ==
 
== DBCONFIG.PHP Modification ==
 +
 +
Make a backup of your /includes/dbconfig.php file.<br />
 +
<br />
 +
Then edit your dbconfig file and look for the following lines:<br />
 +
<code>
 +
function dbconnect(){  <br /> 
 +
global $dbhost,$dbusername,$dbpassword,$dbdatabase; <br />
 +
$db_connect=@mysql_connect($dbhost,$dbusername,$dbpassword) or nosql(); <br />
 +
@mysql_select_db($dbdatabase) or nosql(); <br />
 +
return $db_connect; <br />
 +
} <br />
 +
</code><br />
 +
Add the following line above <code>return $db_connect;</code> <br />
 +
'''mysql_query("SET NAMES 'latin5' COLLATE 'latin5_turkish_ci'");''' <br />
 +
''Be sure to change latin5 and latin5_turkish_ci to the same as what you converted your database character set and collation to.''<br />
 +
<br />
 +
When you are done, that section should now look like the below example:<br />
 +
<code>
 +
function dbconnect(){<br />
 +
global $dbhost,$dbusername,$dbpassword,$dbdatabase;<br />
 +
$db_connect=@mysql_connect($dbhost,$dbusername,$dbpassword) or nosql();<br />
 +
@mysql_select_db($dbdatabase) or nosql();<br />
 +
mysql_query("SET NAMES 'latin5' COLLATE 'latin5_turkish_ci'");<br />
 +
return $db_connect;<br />
 +
}
 +
</code>

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

Charset Description Default collation
big5 Big5 Traditional Chinese big5_chinese_ci
dec8 DEC West European dec8_swedish_ci
cp850 DOS West European cp850_general_ci
hp8 HP West European hp8_english_ci
koi8r KOI8-R Relcom Russian koi8r_general_ci
latin1 cp1252 West European latin1_swedish_ci
latin2 ISO 8859-2 Central European latin2_general_ci
swe7 7bit Swedish swe7_swedish_ci
ascii US ASCII ascii_general_ci
ujis EUC-JP Japanese ujis_japanese_ci
sjis Shift-JIS Japanese sjis_japanese_ci
hebrew ISO 8859-8 Hebrew hebrew_general_ci
tis620 TIS620 Thai tis620_thai_ci
euckr EUC-KR Korean euckr_korean_ci
koi8u KOI8-U Ukrainian koi8u_general_ci
gb2312 GB2312 Simplified Chinese gb2312_chinese_ci
greek ISO 8859-7 Greek greek_general_ci
cp1250 Windows Central European cp1250_general_ci
gbk GBK Simplified Chinese gbk_chinese_ci
latin5 ISO 8859-9 Turkish latin5_turkish_ci
armscii8 ARMSCII-8 Armenian armscii8_general_ci
utf8 UTF-8 Unicode utf8_general_ci
ucs2 UCS-2 Unicode ucs2_general_ci
cp866 DOS Russian cp866_general_ci
keybcs2 DOS Kamenicky Czech-Slovak keybcs2_general_ci
macce Mac Central European macce_general_ci
macroman Mac West European macroman_general_ci
cp852 DOS Central European cp852_general_ci
latin7 ISO 8859-13 Baltic latin7_general_ci
cp1251 Windows Cyrillic cp1251_general_ci
cp1256 Windows Arabic cp1256_general_ci
cp1257 Windows Baltic cp1257_general_ci
binary Binary pseudo charset binary
geostd8 GEOSTD8 Georgian geostd8_general_ci
cp932 SJIS for Windows Japanese cp932_japanese_ci
eucjpms UJIS for Windows Japanese eucjpms_japanese_ci

Extended System Configuration

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

SETTING DESCRIPTION
admin_content_lang Content language for admin area
Used for this Meta tag: <meta http-equiv="Content-Language" content="<?=$admin_content_lang?>">
admin_content_type Content type for admin area. eg - charset=iso-8859-1
Used for this Meta Tag: <meta http-equiv="Content-Type" content="text/html; <?=$admin_content_type?>">
cfcharset Character set override for contact form (Set the charset to use)
content_lang Content language for admin area

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;
}

Retrieved from "https://www.awbswiki.com/index.php?title=Change_Database_Collation&oldid=1220"