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 :
01.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'’','‘');02.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'°','°');03.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'à','à ');04.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'á','á');05.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'â','â');06.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ã','ã');07.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ä','ä');08.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'å','Ã¥');09.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'æ','æ');10.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ç','ç');11.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'è','è');12.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'é','é');13.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ê','ê');14.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ë','ë');15.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ì','ì');16.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'í','Ã' );17.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'î','î');18.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ï','ï');19.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ð','ð');20.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ñ','ñ');21.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ò','ò');22.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ó','ó');23.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ô','ô');24.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'õ','õ');25.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ö','ö');26.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ù','ù');27.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ú','ú');28.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'û','û');29.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ü','ü');30.UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'ý','ý');If you are interested in MySQL (or PHP) recover comments tables and / or comments from the columns of a table, remember that it is very easy to access this information directly from a query.
Retrieving comments for a table :
1.# 'sillysmart' is the name of the database you want to extract information2.mysql> SHOW TABLE STATUS FROM sillysmart 3.mysql> will produce the following informations:
| Name | Engine | Version | Rows | Comment |
|---|---|---|---|---|
| Country | MyISAM | 10 | 240 | Countries Listing |
| News | MyISAM | 10 | 30 | News Listing |
Where the column 'Comment' will give you the information, the commentary of the table. Know that many other informations are available through this application.
Retrieve the comments columns of a table :
1.# 'Country' is the table name you want2.mysql> SHOW FULL COLUMNS FROM Country 3.mysql> will produce the following informations:
| Field | Type | Collation | Null | Key | Extra | Privileges | Comment |
|---|---|---|---|---|---|---|---|
| country_id | bigint(20) | NULL | NO | PRI | auto_increment | select,insert,update | Id |
| country_name | varchar(255) | latin1_swedish_ci | NO | UNI | select,insert,update | Country Name | |
| country_tld | varchar(2) | latin1_swedish_ci | YES | select,insert,update | Country TLD |
Where the column 'Comment' will give you the information, the comment column of the table.
Free for you to use this information in PHP. Personally we use these columns in a transparent way on SillySmart to create generic back-office remains speaking for a non-scientist. The only constraint is that the length of the commentary is limited to 60 characters, so be succinct!
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:
01.<?php02.header("Content-Type: application/vnd.ms-excel; charset=UTF-16LE");03.header("Content-Disposition: attachment; filename=\"Export Excel\"");04.mysql_connect("localhost","root","");05.mysql_select_db("fake");06.$sql = "SELECT * FROM users ORDER BY user_name";07.$req = mysql_query($sql);08.?>09.<html><head></head><body>10. <table>11. <tr>12. <td>Name</td>13. <td>Firstname</td>14. <td>Email</td>15. </tr>16.<?17.while ($row = @mysql_fetch_assoc($req))18.{19.?>20. <tr>21. <td><?=substr(chr(255).chr(254).mb_convert_encoding($row['user_sname'], "UTF-16LE", "UTF-8"),2)?></td>22. <td><?=substr(chr(255).chr(254).mb_convert_encoding($row['user_fname'], "UTF-16LE", "UTF-8"),2)?></td>23. <td><?=substr(chr(255).chr(254).mb_convert_encoding($row['user_email'], "UTF-16LE", "UTF-8"),2)?></td>24. </tr>25.<?26.}27.?>28. </table>29.</body></html>
