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

MySQL utf8_general_ci - utf8_turkish_ci

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
phparmy
Asked:
phparmy
  • 5
  • 3
  • 2
1 Solution
 
NerdsOfTechTechnology ScientistCommented:
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
 
absxCommented:
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
 
phparmyAuthor Commented:
Thanks but i noticed that. Now i want to convert all my old datas. Problem is that.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
NerdsOfTechTechnology ScientistCommented:
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
 
NerdsOfTechTechnology ScientistCommented:

<?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
 
phparmyAuthor Commented:
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
 
NerdsOfTechTechnology ScientistCommented:
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
 
phparmyAuthor Commented:
Is there any program that doing it. or mysql shell commands or ext.
0
 
NerdsOfTechTechnology ScientistCommented:
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
 
absxCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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