Encoding issues for php and mysql

Question

I'm having encoding issues with my LAMP stack.

The web page head has:

My PDO dsn is: mysql:host=localhost;dbname=$database;charset=utf8

The options array is:

options = array (
    // PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
);

I've tried both with and without setting PDO::MYSQL_ATTR_INIT_COMMAND; I get the same results either way.

I retrieve from my db with something like:

$sql = 'SELECT * FROM ...';
$prepared = $dbh->prepare($sql);
$prepared->execute()
while ($row = $prepared->fetch(PDO::FETCH_ASSOC)) {
    ...
}

There are no bound parameters.

The collation for the database, all the tables, and all the string (CHAR, VARCHAR, TEXT) collumns is utf8_unicode_ci.

If I have (for example) 18”x24” in my database (as inserted by phpMyAdmin), I get 18”x24” in input and textarea fields but 18�x24� when echoed outside a form.

If I have 18â€x24â€ in my db, I get 18â€x24â€ in input and textarea fields but 18”x24” when echoed outside a form.

How do I get 18”x24” (not 18�x24� and not 18â€x24â€) to display both inside and outside form fields?

Crystal balling here: are you using htmlspecialchars() or htmlentities() and did you forget to inform those functions you are feeding the utf-8? (3rd parameter for both, the default is ISO-8859-1 for versions before PHP 5.4). — Wrikken
My HTML encoding is utf-8: <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> — user2236890

user2236890 user2236890 · Accepted Answer · 2013-05-23T22:41:49

I have discovered that I badly misdescribed my problem. For example, the inside v. outside form fields part was wrong.

The MySql data was not really UTF-8. I had to fix the data, not the php coding.

Encoding issues for php and mysql

1 Answers