Software is like sex... It's better when it's free.

Blog - Tag 'charset'

Conversion d'une colonne mysql d'iso à utf-8

Si jamais un de vos dump sql à été mal encodé et que vous souhaitez convertir une colonne mysql d'iso à utf-8, voici une petite table de conversion de caractères courants : 

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`,'ý','ý');
Tags:  charset, mysql.
Posté le Mercredi 29 avril 2009 23:04:48 - 0 commentaire
Encodage UTF-8 avec Excel

Si vous avez essayé mainte fois de produire un export excel de données issues d'une base en UTF-8 par PHP, vous êtes sans doute arrivés à des résultats surprenants.

Notre cher ami Microsoft utilise en fait l'encodage UTF-16le.

Ainsi, pour former un document excel (ici une liste d'utilisateurs), il vous "suffit" de faire : 

<?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>Nom</td>
      <td>Prénom</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>
Tags:  charset, microsoft, mysql, php.
Posté le Mardi 28 avril 2009 18:23:05 - 0 commentaire