Character Encoding Mismatch

Question

My scripts are definitely saved in UTF-8. I'm instantiating PDO with "{$this->engine}:host={$this->host};dbname={$this->name};charset=UTF-8". My tables use InnoDB and are collated using utf8_general_ci. My pages are sent either with the Content-Type: text/html; charset=UTF-8 header or the <meta> equivalent.

When using PDO to store a € character originating either from HTTP input or a string literal in the source code, I'm left with c3 a2 e2 80 9a c2 ac according to MySQL Workbench 5.2. Retrieving this from the database and displaying it on the page works fine. Yet in phpMyAdmin and Workbench, I see €.

When using these two tools to store a €, I'm left with e2 82 ac, which is apparently the correct UTF-8 representation, but if I try to retrieve and then output this with PHP, � is displayed.

My question is, from where does this discrepancy arise and is it possible to have my web pages and database tools both work flawlessly?

Answer 1

The charset directive in the DSN actually applies to MSSQL. I simply needed to execute SET NAMES. Sorry about that.

I achieved that thus:

parent::__construct("{$this->engine}:host={$this->host};dbname={$this->name}",
                    $this->user, $this->password,
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"));

Answer 2

simply, convert € to an html entity € for the superfix (but you may get the issue with other special chars)

the other side of the problem (and more specifically) is check the mysql character_set_server and the most common cause character_set_client - seeAlso: connection charsets

also, it's important to note that the meta equiv makes no difference, you need to always set the header('Content-Type: ...)

Answer 3

Since your HTML page is correct, it seems that you're storing the correct data in the database.

€ is the UTF-8 encoding of the Euro sign misinterpreted as windows-1252. It appears that your Windows tools use "ANSI" encoding instead of the database encoding.