Solved

Mysql Character Set

Posted on 2008-10-11
12
516 Views
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?  
0
Comment
Question by:pkonstan1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
12 Comments
 
LVL 13

Accepted Solution

by:
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).
0
 
LVL 12

Expert Comment

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

Author Comment

by:pkonstan1
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?
0
Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

Author Comment

by:pkonstan1
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?
0
 
LVL 13

Expert Comment

by:Xyptilon2
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.
0
 
LVL 12

Assisted Solution

by:jazzIIIlove
jazzIIIlove earned 62 total points
ID: 22697769

<?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
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22697777
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
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22697914
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
 
LVL 12

Expert Comment

by:jazzIIIlove
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:
0
 
LVL 12

Expert Comment

by:jazzIIIlove
ID: 22741407
yes?
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

705 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