PHP:使用PDO从MySQL数据库输出utf8时出现问题 [英] PHP: problems outputting utf8 from MySQL database using PDO
问题描述
$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>");
}
此代码段是应该解析并输出存储在UTF-8中的IRC数据的代码-但是在回显时，任何unicode内容都简单地显示为:
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:
??????
我已经审查了几个问题，并提供了一些声称的修复程序，但是似乎没有任何效果.使用相同数据库的另一个脚本完美地呼应了UTF-8内容，但是它使用的是标准MySQL实现的PHP，而不是PDO.我在做什么错了?
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?
推荐答案
使用UTF8时有多个故障点:
There are multiple points of failure, when working with UTF8:
-
确保表格为utf8
make sure the table is utf8
使用SQL命令Show Variables;检查表的MySQL字符设置.
Use SQL-Command Show Variables; to check the MySQL character settings for the table.
插入的数据为utf8
inserted data is utf8
您可以尝试使用 phpMyAdminer 或 Adminer 之类的工具来检查
数据已正确插入.尝试通过该工具手动插入数据.
通过脚本插入时，请勿使用基本的字符串函数.始终使用他们的mbstring
备择方案.设置mb_internal_encoding( 'UTF-8' );允许PHP在内部处理UTF-8.
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.
获取的数据是utf8
fetched data is utf8
我只会使用$pdo->exec("SET NAMES utf8");并删除其余部分.因为
SET NAMES x等效于
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;
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;
显示的数据为utf8
displayed data is utf8
如果此内容显示在html页面中，请不要忘记设置meta标签或标头
"Content-Type: text/html; charset=utf-8".
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".
这篇关于PHP:使用PDO从MySQL数据库输出utf8时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
