MySQL collation

Question

I faced a very strange problem when developing on PHP and MySQL. I had such problem 4-5 years ago, but since than not. And really don't remember how I solved it.

Well... the problem: I'm having database with collation of utf_unicode_ci, but when inserting georgian letters: სახელი და გვარი in the database I'm having áƒ›áƒ˜áƒ®áƒ”áƒ˜áƒ <- this symbols. What could the problem be? and how to solve it?

Any ideas?

Take a look at this thread: http://stackoverflow.com/questions/7073401/problem-with-php-and-mysql-utf-8-special-character/7073506#7073506 I think my answer there might help explain a possible issue. — gview, Sep 09 '11 at 19:54

score 3 · Accepted Answer · answered Sep 16 '11 at 20:14

According to Eray's answer I realized that utf8_general_ci shuld be set in collation and not utf8_unicode_ci

and in addition to that use mysql_set_charset('utf8', $connection) function.

Here's the sample:

        $connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS);
    mysql_set_charset('utf8',$connection);      
    if (!$connection) {
        die("Database connection failed: " . mysql_error());
    } else {
        $db_select = mysql_select_db(DB_NAME, $connection);
        if (!$db_select) {
            die("Database selection failed: " . mysql_error());
        }
    }
}

So having mysql_set_charset on the second line solved my issue.

Hope it helps anyone..

score 2 · Answer 2 · edited Sep 16 '11 at 20:26

2

Can you try utf8_general_ci instead of this ? It will solve your problem.

But don't forget , *databases* and *tables* collation must be utf8_general_ci.

edited Sep 16 '11 at 20:26

Vishwanath Dalvi

35,388
41
123
155

answered Sep 09 '11 at 19:49

Eray

7,038
16
70
120

1

What does collation (sort order) have to do with the wrong character set being displayed? – gview Sep 09 '11 at 19:56
the collation of the table determines the sort order. It has nothing to do with this problem. – gview Sep 09 '11 at 20:15
problem isn't about sorting ? – Eray Sep 10 '11 at 00:15

score 0 · Answer 3 · answered Sep 09 '11 at 19:52

Make sure you're using one of the string types with N prefix (NCHAR, NVARCHAR).

Don't forget to add N to the parameters you are passing to the database when adding data. For example:

INSERT INTO table VALUES (N'Georgian Letters', N'Georgian Letters', ...)

The N prefix will tell sql its subsequent string is a unicode string.

score 0 · Answer 4 · answered Sep 09 '11 at 19:57

0

One thing that I can make you sure of is that the problem you are experiencing doesn't have anything to do with collation. It is related to character set and encoding and most probably the problem is not when inserting the data into database but when you read the data and display them, there are many questions about unicode characters in mysql on SO do a search and you'll find tons of tips and information.

answered Sep 09 '11 at 19:57

nobody

10,599
4
26
43

collation is linked to character set and with collation you can set charset as well – Your Common Sense Sep 09 '11 at 20:01
When "სახელი და გვარი" is displayed as "áƒ›áƒ˜áƒ®áƒ”áƒ˜áƒ" it is definitely not related to collation, collation is taken into account only when comparing strings. – nobody Sep 09 '11 at 20:05
collation is based on charset. you can't use utf8_general_ci collation without having set utf8 charset. – Your Common Sense Sep 09 '11 at 20:10
@Shrapnel: Yes, collation is based on character set, but the problem here is that he's inserting characters in one character set, and they are showing up in another. Changing collation is going to do absolutely nothing. The problem is most likely that the client character set being used by php is not utf8, and the characters are being incorrectly translated and stored. – gview Sep 09 '11 at 20:23

MySQL collation

4 Answers4