PHP: problems outputting utf8 from MySQL database

Question

$dbo = new PDO("mysql:host=localhost;dbname=database", "databaseuser",
  "databasepassword", array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$dbo -> exec("set character_set_client='utf8'");
$dbo -> exec("set character_set_results='utf8'");
$dbo -> exec("set collation_connection='utf8_general_ci'");
$prepSnapRetrieve = $dbo->prepare(
  "SELECT * FROM znc_log WHERE `nick` LIKE :u AND `tstamp` BETWEEN :t1 AND :t2"
);
$prepSnapRetrieve->execute(array(':u' => str_replace("|osu","", $_GET['u']),
  ':t1' => $_GET['sns'], ':t2' => $_GET['sne']));
$snapshotListing = $prepSnapRetrieve->fetchAll();
echo("Snapshot of ".$_GET['u']. "'s chat on ".strftime("%e/%m/%g",$_GET['sne']));
echo("<br><br>");
foreach($snapshotListing as $chat) {
  echo "<i>" . " " . "[" . strftime("%H:%M:%S", $chat['tstamp']) . "]" .
   "</i> " . " ><u>" . $chat['channel'] . "</u>< " . "(<b>" .
   htmlspecialchars($chat['nick'])."</b>) ".htmlspecialchars($chat['message']);
  echo("<br>");
}

This snippet is code is supposed to parse and output IRC data which has been stored in UTF-8 - but upon echoing, any unicode content simply appears as:

??????

I have reviewed several questions and included several purported fixes, but nothing appears to work. Another script using the same database echoes UTF-8 content perfectly, but it is using the standard MySQL implementation of PHP, and not PDO. What am I doing wrong?

Answer 1

There are multiple points of failure, when working with UTF8:

• make sure the table is utf8

  Use SQL-Command Show Variables; to check the MySQL character settings for the table.

• inserted data is utf8

  You might try tools like phpMyAdminer or Adminer to check, if the
  data is inserted correctly. Try to insert data via the tool manually. When inserting via script, do not use basic string functions. Always use their mbstring alternatives. Set mb_internal_encoding( 'UTF-8' ); to let PHP handle UTF-8 internally.

• fetched data is utf8

  I would only use $pdo->exec("SET NAMES utf8"); and drop the rest. Because SET NAMES x is equivalent to SET character_set_client = x; SET character_set_results = x; SET character_set_connection = x;

• displayed data is utf8

  If this is displayed in an html page, do not forget to set the meta tag or header
  "Content-Type: text/html; charset=utf-8".