If one of your sql dump was poorly encoded and you want to convert a mysql column from iso to utf-8, here is a small conversion table for common characters :
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'’','‘'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'°','°'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'à','à '); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'á','á'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'â','â'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ã','ã'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ä','ä'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'å','Ã¥'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'æ','æ'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ç','ç'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'è','è'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'é','é'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ê','ê'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ë','ë'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ì','ì'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'í','Ã' ); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'î','î'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ï','ï'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ð','ð'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ñ','ñ'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ò','ò'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ó','ó'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ô','ô'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'õ','õ'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ö','ö'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ù','ù'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ú','ú'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'û','û'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ü','ü'); UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ý','ý');
If you have tried repeatedly to produce an excel export of data from a database in UTF-8 with PHP, you probably came to some surprising results.
Our dear friend Microsoft uses in fact the encoding UTF-16le.
Thus, to form a excel document (here a list of users), "simply" do:
<?php header("Content-Type: application/vnd.ms-excel; charset=UTF-16LE"); header("Content-Disposition: attachment; filename=\"Export Excel\""); mysql_connect("localhost","root",""); mysql_select_db("fake"); $sql = "SELECT * FROM users ORDER BY user_name"; $req = mysql_query($sql); ?> <html><head></head><body> <table> <tr> <td>Name</td> <td>Firstname</td> <td>Email</td> </tr> <? while ($row = @mysql_fetch_assoc($req)) { ?> <tr> <td><?=substr(chr(255).chr(254).mb_convert_encoding($row['user_sname'], "UTF-16LE", "UTF-8"),2)?></td> <td><?=substr(chr(255).chr(254).mb_convert_encoding($row['user_fname'], "UTF-16LE", "UTF-8"),2)?></td> <td><?=substr(chr(255).chr(254).mb_convert_encoding($row['user_email'], "UTF-16LE", "UTF-8"),2)?></td> </tr> <? } ?> </table> </body></html>