Mysql Character Set

Posted on 2008-10-11
Last Modified: 2010-08-05
I am working on a project that will require text to be stored in multiple languages:
$language_1 = array("English", "French", "German", "Portuguese", "Spanish", "Swahili");
$language_2 = array("Russian", "Mandarin", Arabic");

I believe the choice of "latin1" character set & "latin1" collation will work for the languge_1 options.  But what about language_2?  Is there any way those langages can be stored in the same database or will it require a completely separate set of databases with a different character set and collation properties?

The specific application is that a user arrives at a home page where they are given the option of choosing the language in which they would like to read the instructions.  Once they choose, the welcome letter is displayed on the screen and in the language they selected.  They then continue to a form where they fill out their personal info.  This form again is in their langague.  The info they enter must then be stored in a database in the language in which they are working.

So, for example, can the Russian and Spanish text share the same database or must there be two separate databases, each with their own character set?  Or is it possilble to have a default character set but then an override for a specific row of a table?  
Question by:pkonstan1
  • 6
  • 2
  • 2
LVL 13

Accepted Solution

Xyptilon2 earned 63 total points
ID: 22693657
They can share the same database provided you use UTF-8 as the character encoding in the relevant table. latin1 a.k.a. ISO 8859-1 does not contain all the non-western characters (as you mentioned yourself).
LVL 12

Expert Comment

ID: 22696249
yes, as Xyptilon2 said, UTF-8 rocks! Use it not only in your db but also in your php files...

Author Comment

ID: 22696901
Jazziiiilove, what do you mean by using it in your php files?  Can you give an illustration of where it would be used in php?
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 22696904
Xyptilon2:, is it possible to convert an existing DB or will I have to reload the data into a new database. Most of this project is starting from scratch, but I'd like to use one tale that has a lot in it?
LVL 13

Expert Comment

ID: 22697296
Yes it's possible, you can make an SQL dump, change the table to UTF-8 and import the dump again.

There are also tools avalaible that can change the character encoding of text files (sql dumps), what comes to mind is recode for linux.
LVL 12

Assisted Solution

jazzIIIlove earned 62 total points
ID: 22697769

// Start output buffering.
// Initialize a session.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Transitions Survey Database Export Page</title>
mysql_query("SET CHARACTER SET UTF-8"); 
mysql_query("SET collation_connection = 'utf8_turkish_ci'");

Open in new window

LVL 12

Expert Comment

ID: 22697777
Alumnide kulland11m kod:

//$alumniFirstNameUpdate = strtr($alumniFirstNameUpdate, "ç1ö_üÇ0Ö^Ü", "cgiosuCGIOSU");
                 //$alumniFirstNameUpdate = strtr($alumniFirstNameUpdate, "ç&#287;&#305;ö&#351;üÇ&#286;&#304;Ö&#350;Ü", "cgiosuCGIOSU");

Veya basit bir replace kodu yazd1m:

$old=array("_","^","","","0","1","Ç","ç","Ö","ö" ,"Ü","ü");

$new=array("s","s","g","g","i","i","c","c","o","o" ,"u","u");

$taglist = str_replace( $old, $new, $taglist );

Yukar1s1 geçici çözüm, a_a1s1 kesin çözüm:

Mant1kl1 bir design:

3 phases:

1)      Making DB UTF-8
2)      Making UTF-8 of connection bw. MySQL and  PHP
3)      Making output UTF-8

1)         DB level:
$username = username;
$password = change_to_your_password;
$hostname = localhost;
$dbh = mysql_connect($hostname, $username, $password) or die(Unable to connect to MySQL);
@mysql_select_db(your_database_name,$dbh) or die(Could not select first_test);
$tables=mysql_query(SHOW TABLES;);
while($row = mysql_fetch_row($tables))
 $table = $row[0];
 //$changes = array();
 if(mysql_query(CREATE TABLE conv_{$table} LIKE {$table};) === false){
  echo Failed creating conv_{$table}:, mysql_error(), \n;
 if(mysql_query(ALTER TABLE conv_$table DEFAULT CHARACTER SET=utf8;) === false){
  echo Failed Altering conv_{$table}:, mysql_error(), \n;
 // copy the data into the new table, transcoding to UTF8
 $fields = mysql_query(DESCRIBE {$table};);
 $allfields = $selectfields = array();
 while($field = mysql_fetch_array($fields)){
  $allfields[] = $field['Field'];
  $selectfields[] = (stripos($field['Type'], char) !== false || stripos($field['Type'], text) !== false) ? CONVERT({$field['Field']} USING utf8) : {$field['Field']};
 if(mysql_query(INSERT INTO conv_{$table} (. implode($allfields, , ) .) SELECT . implode($selectfields, , ) . FROM {$table};) === false){
  echo Failed Insert into conv_{$table}:, mysql_error(), \n;
 if(mysql_query(RENAME TABLE {$table} TO preutf8_{$table};) === false){
  echo Failed rename {$table} to preutf8_{$table}:, mysql_error(), \n;
 if(mysql_query(RENAME TABLE conv_{$table} TO {$table};) === false){
  echo Failed rename conv_{$table} to {$table}:, mysql_error(), \n;
 //echo implode($changes, ;\n);
2)         PHP MYSQL relation:

Bu mesela konfigürasyon dosyan1z varsa uygundur&
var $default = array(
              driver => mysql,
              persistent => false,
              host => localhost,
              login => username,
              password => my_password,
              database => mydatabase,
              prefix => ,
              encoding => utf82

3)      HTML tag editing:
  <META http-equiv=Content-Type Content=text/html; charset=utf-83>
LVL 12

Expert Comment

ID: 22697914
oh sorry:

line 18: mysql_query("SET collation_connection = 'utf8_turkish_ci');
of course, you set your desired character set...

Bu mesela konfigürasyon dosyan1z varsa uygundur&
meaning, if you have a configuration file you have, it's suitable...
LVL 12

Expert Comment

ID: 22697919
edit3: Veya basit bir replace kodu yazd1m:
I wrote a simple replace code...

edit4:Yukar1s1 geçici çözüm, a_a1s1 kesin çözüm:
Mant1kl1 bir design:

Above solution is temp, good solution below:
A sensible design:
LVL 12

Expert Comment

ID: 22741407

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Optimize the query 5 43
what do I need to host my own web sites? 13 50
PHP Curl to output a url 7 47
MySQL InnodDB Import from mysqldump takes forever. 2 35
Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question