Incorrect string encodings
- by James
Note: I have read all of the related PHP, UTF-8, character encoding articles that are usually suggested, but my question relates to data inserted before I applied such techniques. I am wishing to retrospectively fix all character encoding problems.
Now all connections are set as utf8 using PDO.
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
Unfortunately, a large amount of data was inserted that is of questionable encoding before I had implemented correct character encoding practices. As displayed by:
$sql = "SELECT name FROM data LIMIT 3";
foreach ($pdo->query($sql) as $row)
{
$name = $row['name'];
echo $name . "\n";
echo utf8_encode($name) . "\n";
echo utf8_decode($name) . "\n";
echo htmlspecialchars($name, ENT_QUOTES, 'UTF-8') . "\n";
echo htmlspecialchars(utf8_encode($name), ENT_QUOTES, 'UTF-8') . "\n";
echo htmlspecialchars(utf8_decode($name), ENT_QUOTES, 'UTF-8') . "\n";
echo '<hr/>';
}
Which produces:
AntonÃÂn Dvořák
AntonÃÆÃÂn DvoÃâ¦Ãâ¢ÃÆák
Anton??n Dvo??????¡k
AntonÃÂn Dvořák
AntonÃÆÃÂn DvoÃâ¦Ãâ¢ÃÆák
----------
Ô±Ö€Õ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿Ö€ÕµÕ¡Õ¶
ñÃâ¬Ã¡Ã´ ýáùáÿÃâ¬ÃµÃ¡Ã¶
????? ??????????
Ô±Ö€Õ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿Ö€ÕµÕ¡Õ¶
ñÃâ¬Ã¡Ã´ ýáùáÿÃâ¬ÃµÃ¡Ã¶
----------
Tiësto
Tiësto
Tiësto
Tiësto
Tiësto
Tiësto
----------
When removing 'SET NAMES utf8' with PDO it produces the data:
AntonÃn DvoÅák
AntonÃÂn DvoÃÂák
Antonín Dvorák
AntonÃn DvoÅák
AntonÃÂn DvoÃÂák
Antonín Dvorák
----------
???? ?????????
Ô±ÖÕ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿ÖÕµÕ¡Õ¶
???? ?????????
???? ?????????
Ô±ÖÕ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿ÖÕµÕ¡Õ¶
???? ?????????
----------
Tiësto
Tiësto
Ti?sto
Tiësto
Tiësto
----------
And here is a dump of the database rows concerned:
DROP TABLE IF EXISTS `data`;
CREATE TABLE IF NOT EXISTS `data` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(80) NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`(10)),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
INSERT INTO `data` (`id`, `name`) VALUES (0, 'AntonÃÂn Dvořák'), (1, 'Ô±Ö€Õ¡Õ´ Ô½Õ¡Õ¹Õ¡Õ¿Ö€ÕµÕ¡Õ¶'), (2, 'Tiësto');
The 3rd and 6th lines of the 3rd row "Tiësto" are then correctly echoed. I'm just unsure what is the best way to correct encodings/detect the encodings of bad strings and correct, etc.
