MySQL UTF-8 coding

Question

I'm very confused with the char coding of my database.

I have the database configure with UTF8_general_ci. But If I try to save something in spanish like "león" the content si spoiled then to fix it I have to use utf8_decode before save and when I read I use utf8_encode.

Then the spanish is correct.

Now I have russian users writing in cyrilic and his content is spoiled (with ???????)

I suppose if I don't use utf8_decode the cyrilic will be correct (or not I don't know) but then the spanish will be not correct.

What can I do to save correctly all the languages? I'm very confused.

What character set does the client/connection use ?

Radu Murzea
– Radu Murzea

2012-05-04 15:46:32 +00:00
Commented May 4, 2012 at 15:46 — Radu Murzea
– Radu Murzea, Commented May 4, 2012 at 15:46

rekire · Accepted Answer · 2012-05-04 15:47:07Z

1

Try to run as first statment this sql query:

set names 'utf8';

This sets the encoding of the MySQL connetion to UTF-8 and prevents a double encoding.

answered May 4, 2012 at 15:47

rekire

48.1k34 gold badges184 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oscardr Over a year ago

Thank you!! I've putted after the connection: mysql_query ("SET NAMES 'utf8'"); and it seems it works perfect!

Pavel Strakhov · Accepted Answer · 2012-05-04 15:48:06Z

1

Check that your client executes

SET NAMES 'utf8';

when it starts a session. Check that all tables and all char fields in tables use utf-8 encoding (their encoding can differ from database encoding).

answered May 4, 2012 at 15:48

Pavel Strakhov

40.8k6 gold badges93 silver badges131 bronze badges

Comments

Taha Paksu · Accepted Answer · 2012-05-04 15:48:22Z

1

I use a script that converts all the tables in a database for utf8 charsets. Give it a try:

<?
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'db_name';
$collation = "utf8_general_ci";
$charset = "utf8";

mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);

mysql_query("ALTER DATABASE $dbname COLLATE $collation");
$result = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_row($result)) {
mysql_query("ALTER TABLE $row[0] COLLATE $collation");
$result1 = mysql_query("SHOW COLUMNS FROM $row[0]");
while ($row1 = mysql_fetch_assoc($result1)) {
if (preg_match('~char|text|enum|set~', $row1["Type"])) {
mysql_query("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] CHARACTER SET $charset");
mysql_query("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] COLLATE $collation DEFAULT '$row1[Default]'");
echo $row[0] . "->" . $row1[Field] . "->" . $row1[Type] . " is now UTF8
";
}
}
}
mysql_free_result($result);

?>

answered May 4, 2012 at 15:48

Taha Paksu

15.6k2 gold badges50 silver badges83 bronze badges

2 Comments

Oscardr Over a year ago

Thannk you But it's supposed I have the database in UTF-8, I defined the fields in UTF-8 I don't understand why I have to alter the database to UTF-8.

Oscardr Over a year ago

I've needed a quick solution and I added after the connection mysql_query ("SET NAMES 'utf8'"); And it seems works well, I'm more relaxed now. Now I'll read carefully your links and I'll try to understand the tpasku script. Thank you, you are awesome!!! Oscar.

Sanath · Accepted Answer · 2012-05-04 15:54:35Z

1

 http://stackoverflow.com/questions/367711/what-is-the-best-collation-to-use-for-mysql-with-php

mysql character sets -----------------

 http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

you can specify a more specific UTF-8 character set like 'utf8_swedish_ci'

answered May 4, 2012 at 15:54

Sanath

4,91211 gold badges58 silver badges88 bronze badges

1 Comment

Oscardr Over a year ago

I've needed a quick solution and I added after the connection mysql_query ("SET NAMES 'utf8'"); And it seems works well, I'm more relaxed now. Now I'll read carefully your links and I'll try to understand the tpasku script. Thank you, you are awesome!!! Oscar.

Collectives™ on Stack Overflow

MySQL UTF-8 coding

4 Answers 4

1 Comment

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related