Solved

Mysql Character Set

Posted on 2008-10-11
12
510 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
  • 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now