Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL utf8_general_ci - utf8_turkish_ci

Posted on 2008-10-21
10
Medium Priority
?
1,206 Views
Last Modified: 2013-12-13
year ago i programmed a site. database is mysql i set charset utf8_general_ci. My page charset is utf8. when i inserting "Çevre ve Ye_illendirme" text to database via form. In database i see like that "Ã!evre ve YeÅxillendirme " but i want to see it "Çevre ve Ye_illendirme" now i found that i have to set my charset utf8_turkish_ci. But i have a lot of data in database how can i convert all data correctly.
0
Comment
Question by:phparmy
  • 5
  • 3
  • 2
10 Comments
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 22765952
Always backup your database before attempting to alter any charsets.

If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Warning: The preceding operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8)."

Use much caution.
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Open in new window

0
 
LVL 9

Expert Comment

by:absx
ID: 22766016
I don't think either Turkish or generic UTF-8 encodings have a problem with those characters.

This could also be a "double-encoding" problem, common to PHP and MySQL applications. As user fills the form and PHP gets the strings (still encoded in UTF-8) and passes them on to MySQL, unless the communication encoding is set to UTF-8, MySQL thinks it's getting a ISO-8859-1 stream and re-encodes every character to UTF-8, therefore effectively double-encoding the data before inserting.

As PHP's MySQL connections use ISO-8859-1 as the default communication encoding, in UTF-8 applications the developer needs to define UTF-8 to be used in communication. This can be done using the command
mysql_set_charset('utf8',$mysql_connection);
right after opening the connection with mysql_connect().

Previous poster's example should work for converting the double-encoded data back to normal.
0
 

Author Comment

by:phparmy
ID: 22774405
Thanks but i noticed that. Now i want to convert all my old datas. Problem is that.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 22774695
Backup first.
Here's a ALL tables in database solution via PHP :)
<?php
/* fill in your database name */
$database_name = "my_db";
 
/* connect to MySQL */
if (!$link = mysql_connect("db_host_name", "username", "pass")) {
  die("Could not connect: " . mysql_error());
}
 
/* query all tables */
$sql = "SHOW TABLES FROM $database_name";
if($result = mysql_query($sql)){
  /* add table name to array */
  while($row = mysql_fetch_row($result)){
    $found_tables[]=$row[0];
  }
}
else{
  die("Error, could not list tables. MySQL Error: " . mysql_error());
}
 
/* loop through and drop each table */
foreach($found_tables as $table_name){
  $sql = "ALTER TABLE $database_name.$table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_turkish_ci";
  if($result = mysql_query($sql)){
    echo "Success - table $table_name charset.";
  }
  else{
    echo "Error charset $table_name. MySQL Error: " . mysql_error() . "";
  }
}
?>

Open in new window

0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 22774705

<?php
/* fill in your database name */
$database_name = "my_db";
 
/* connect to MySQL */
if (!$link = mysql_connect("db_host_name", "username", "pass")) {
  die("Could not connect: " . mysql_error());
}
 
/* query all tables */
$sql = "SHOW TABLES FROM $database_name";
if($result = mysql_query($sql)){
  /* add table name to array */
  while($row = mysql_fetch_row($result)){
    $found_tables[]=$row[0];
  }
}
else{
  die("Error, could not list tables. MySQL Error: " . mysql_error());
}
 
/* loop through and change charset for each table */
foreach($found_tables as $table_name){
  $sql = "ALTER TABLE $database_name.$table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_turkish_ci";
  if($result = mysql_query($sql)){
    echo "Success - table $table_name charset.";
  }
  else{
    echo "Error charset $table_name. MySQL Error: " . mysql_error() . "";
  }
}
?>

Open in new window

0
 

Author Comment

by:phparmy
ID: 22774752
No you dont understand me. I have done before this manually. I want to data in tables can be converted. Data in tables are not organized for turkish tables was latin1_swedish_ci and my php page was utf8 i inserted data via form now all data are disorganized. Now i want to these data to convert true turkish data in database.
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 22774802
The true answer to your question is that you must now *match patterns* and replace them with correct patterns.

Ã! = Ç
Åx = _
... etc

Sounds like a lot of work. Good luck.
0
 

Author Comment

by:phparmy
ID: 22775174
Is there any program that doing it. or mysql shell commands or ext.
0
 
LVL 20

Expert Comment

by:NerdsOfTech
ID: 22775207
Pretty much just a bunch of your own SQL UPDATE commands to make it work.

Hopefully the encoding is not too bad.

Still sounds like a lot of work.
0
 
LVL 9

Accepted Solution

by:
absx earned 1500 total points
ID: 22775293
Hi,

Neil from byteflex.co.uk addresses this matter in his blog: http://www.byteflex.co.uk/en/fun_with_utf8_php_and_mysql.html

He doesn't provide a cut and paste solution, but a nice-looking pseudo code example you can wrap into PHP:

SET NAMES 'latin1';
SELECT id, col1, col2, col3 FROM table;
SET NAMES 'utf8';
for each row
      INSERT INTO table (col1, col2, col3) VALUES (, , ) WHERE id = ;

Basically, create a script that reads tables to arrays in ISO-8859-1 and rewrite them in UTF-8.

Hope that helps.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

783 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