• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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?  
0
Paul Konstanski
Asked:
Paul Konstanski
  • 6
  • 2
  • 2
2 Solutions
 
Xyptilon2Commented:
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).
0
 
jazzIIIloveCommented:
yes, as Xyptilon2 said, UTF-8 rocks! Use it not only in your db but also in your php files...
0
 
Paul KonstanskiProject SpecialistAuthor Commented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Paul KonstanskiProject SpecialistAuthor Commented:
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?
0
 
Xyptilon2Commented:
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.
0
 
jazzIIIloveCommented:

<?php
// Start output buffering.
ob_start();
// Initialize a session.
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Transitions Survey Database Export Page</title>
</head>
 
 
*****************
 
mysql_query("SET CHARACTER SET UTF-8"); 
mysql_query("SET collation_connection = 'utf8_turkish_ci'");

Open in new window

0
 
jazzIIIloveCommented:
Alumnide kulland11m kod:

//$alumniFirstNameUpdate = strtr($alumniFirstNameUpdate, "ç1ö_üÇ0Ö^Ü", "cgiosuCGIOSU");
                 //latinChars($alumniFirstNameUpdate);
                 //$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:
<?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>
0
 
jazzIIIloveCommented:
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...
0
 
jazzIIIloveCommented:
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:
0
 
jazzIIIloveCommented:
yes?
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now