Solved

MySQL utf8_general_ci - utf8_turkish_ci

Posted on 2008-10-21
10
1,165 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 19

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 19

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 19

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 19

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 19

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 500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
XML extra information 8 30
Help With Simple Database Design 7 50
How do I show metrics with PHP or Javascript 6 34
AJAX Wordpress Not Reading Variable 2 32
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…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 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…

821 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