UTF-8 Character encoding horror

I encountered a major character encoding issue on a greek website. Greek UTF-8 characters became garbled while both mysql and php were treating them correctly as utf-8 characters. In phpmyadmin all field were unreadable as well. Initially i tried dumping the database, converting it with various text editors and iconv but neither helped.
Then i have found this article which more or less explained the problem.
https://coderwall.com/p/gjyuwg/mysql-convert-encoding-to-utf8-without-garbled-data

This query appeared to fix the characters by re-encoding them to utf-8.
SELECT CONVERT(CAST(CONVERT(field USING latin1) AS BINARY) USING utf8) FROM table

However it needs to be run for EVERY table and field in the db. Therefore i’ve put together a small php
script to automate the process. Let me know if it helped in your case!

$link = mysqli_connect("hostname", "dbuser", "password", "database");
if ($link){
$tableList = array();
$res = mysqli_query($link,"SHOW TABLES;");
while($cRow = mysqli_fetch_array($res))
{
$tableList[] = $cRow[0];
}
$fields= array();
foreach ($tableList as $table)
{
$res2 = mysqli_query($link,"SELECT * FROM ".$table." LIMIT 1;");
while ($fields = mysqli_fetch_field($res2))
{
$fixquery = "UPDATE ".$table." SET ".$fields->name." = CONVERT(CAST(CONVERT(".$fields->name." USING latin1) AS BINARY ) USING utf8)";
mysqli_query($link, $fixquery);
}
}
unset($fields);
}

Print Friendly

You may also like...