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

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.

3 ans de développement Web

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 !
Tags:  ajaxjsonmysqlphpsillysmartwandixmlxpathxsl.
Posted the Monday 03 january 2011 15:56:16 - 0 comment

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`,'ý','ý');
Tags:  charsetmysql.
Posted the Wednesday 29 april 2009 23:04:48 - 0 comment

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 

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

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!

Tags:  mysqlphp.
Posted the Monday 08 june 2009 15:45:08 - 1 comment

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:

header("Content-Type: application/; charset=UTF-16LE");
header("Content-Disposition: attachment; filename=\"Export Excel\"");
$sql = "SELECT * FROM users ORDER BY user_name";
$req = mysql_query($sql);
while ($row = @mysql_fetch_assoc($req))
      <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>
Tags:  charsetmicrosoftmysqlphp.
Posted the Tuesday 28 april 2009 18:23:05 - 0 comment