Solved

MySQL utf8_general_ci - utf8_turkish_ci

Posted on 2008-10-21
10
1,174 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
[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
  • 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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 a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

738 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