PHP MySQL Encoding Bug?

Question

Heres my problem. I have a mysql table called quotes. In one of the rows, a quote contains the folloqing characters

‘ and ’

Now the row collation is utf8__unicode__ci

When using MySQL Query Browser and PHPMyAdmin to retrive the rows the quotes come out as intended. How ever when i retrive them from the database using PHP and display them on the screen they come out as boxes

� and �

My html page has UTF-8 encoding, and all the UTF-8 encoding options are set in php:

/* Set UTF-8 settings */
mb_internal_encoding('UTF-8');
mb_regex_encoding('UTF-8');
mb_http_output('UTF-8');

/* Set ICONV ini settings */
ini_set('iconv.input_encoding', 'UTF-8');
ini_set('iconv.output_encoding', 'UTF-8');
ini_set('iconv.internal_encoding', 'UTF-8');

/* Set ICONV function settings */
iconv_set_encoding('input_encoding', 'UTF-8');
iconv_set_encoding('output_encoding', 'UTF-8');
iconv_set_encoding('internal_encoding', 'UTF-8');

It seems to me that it should work. But it doesnt :S can anyone shed some light onto this please.

Answer 1

Your collation isn't helping you; it's just determining sort order. You need the default character set to be utf-8, as in:

CREATE TABLE `foo` (
    `id` int not null auto_increment,
    `name` varchar(50)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

You should be able to address this with:

ALTER TABLE `foo` DEFAULT CHARSET=utf8

Only gotcha is that sometimes your indexes would be too long if they're in utf-8.

Answer 2

You may need to set UTF-8 as the chosen charset on your MySQL connection. If you are using the mysqli PHP driver this means something like this:

$mysqli = new mysqli("localhost", "my_user", "my_password", "test");
$mysqli->set_charset("utf8");

Answer 3

@Joakim, thanks so much for setting me in the right direction. I couldnt use your exact code because im using a custom database driver and it works using the old mysql commands not the mysqli class. I tried using mysql_set_charset() but my php version didnt support it (still php 5 tho).

this is the solution i used:

mysql_query("SET CHARACTER SET utf8", $this->connection);
mysql_query("SET NAMES utf8", $this->connection);