Blog - Tag 'mysql'

As everyone has seen, this is not easy to find time to write news but ..

Here it is, the year 2011 began, the perfect opportunity to make a first report after more than three years spent in intensive web development alongside SillySmart and now Wandi.

The first year of Wandi was very rich with the completion of more than 30 websites.

A big thank you to all those who trust us and allow to advance the adventure with Wandi and SillySmart.

Not forgetting all those who are with us every day: the Wandiz, Charlie, Jeremy, Mickey, Chatton, Nsn guys and all others..

Happy New Year !

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 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 :

# 'sillysmart' is the name of the database you want to extract information
mysql> SHOW TABLE STATUS FROM sillysmart 
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 :

# 'Country' is the table name you want
mysql> SHOW FULL COLUMNS FROM Country 
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:

<?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>