Solved

MySQL utf8_general_ci - utf8_turkish_ci

Posted on 2008-10-21
10
1,150 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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…
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 …

705 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

14 Experts available now in Live!

Get 1:1 Help Now