asked on

Mysql Character Set

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?

ASKER CERTIFIED SOLUTION

Xyptilon2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jazzIIIlove

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

Paul Konstanski

ASKER

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?

Paul Konstanski

ASKER

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?

Xyptilon2

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.

SOLUTION

jazzIIIlove

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jazzIIIlove

Alumnide kulland11m kod:

//$alumniFirstNameUpdate = strtr($alumniFirstNameUpdate, "ç1ö_üÇ0Ö^Ü", "cgiosuCGIOSU");
//latinChars($alumniFirstNameUpdate);
//$alumniFirstNameUpdate = strtr($alumniFirstNameUpdate, "çğıöşüÇĞİÖŞÜ", "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:
<?php
$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;
continue;
}
if(mysql_query(ALTER TABLE conv_$table DEFAULT CHARACTER SET=utf8;) === false){
echo Failed Altering conv_{$table}:, mysql_error(), \n;
continue;
}

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

if(mysql_query(RENAME TABLE {$table} TO preutf8_{$table};) === false){
echo Failed rename {$table} to preutf8_{$table}:, mysql_error(), \n;
continue;
}

if(mysql_query(RENAME TABLE conv_{$table} TO {$table};) === false){
echo Failed rename conv_{$table} to {$table}:, mysql_error(), \n;
continue;
}
//echo implode($changes, ;\n);
}
mysql_close($dbh);
?>
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>

jazzIIIlove

oh sorry:

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

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

jazzIIIlove

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:

jazzIIIlove

yes?