How to store characters like ♥☆ to DB?

Question

Previous issue - was not able to store non-english characters:

How to store non-english characters?

That was fixed by using UTF8. But realized today that symbols like ♥☆ are not stored correctly. They get converted to characters like ♥☆.

How can this be fixed?

Answer 1

It looks to me like they're being stored correctly, but that you're not interpreting them correctly when you read them out. ♥ and ☆ are going to end up as multibyte characters in UTF-8 encoding. I'll bet if you look up that multibyte encoding, you'll see it's the same as the single-byte encoding for ♥ and ☆ respectively.

Edit: adding details.

As you can see in the following table, interpreting the UTF-8 characters as if they were encoded as Windows Latin-1 gives the results you're seeing.

UTF-8 character      Hex
♥                    e2 99 a5
☆                    e2 98 86

Windows Latin-1      Hex
â                    e2
™                    99
¥                    a5
˜                    98
†                    86

Answer 2

Is UTF8 used consistently across the whole spectrum (MySQL, PHP, Apache, <meta>s, headers..)?

For me this worked out of the box:

$query = "update tbl set col = '♥☆' where id = 1";
mysql_query($query) or die(mysql_error());
$query = "select col from tbl where id = 1";
$res = mysql_query($query) or die(mysql_error());
print_r(mysql_fetch_row($res));

Debug output:

Content-type: text/html; charset=utf-8
Array
(
    [0] => ♥☆
)