Feb 11 2010

PHP function to fix collation on database fields of MySQL

Category: MySql,Php — Giulio Pons @ 10:50 am

This PHP function search for tables in the active db, match table’s name with the regular expression passed as first parameters and if it matches alter the table to convert charcter set and collation.

This can be usefull when you have a db that you’ve created fast, without thinking, and than you discover: “Ouch! I’ve setted the bad collation!“.

function fix_encoding($reg='//',$set='utf8',$collation='utf8_general_ci') {
	$res = mysql_query("SHOW TABLES");
	while ($row = mysql_fetch_row($res)) {
		if (preg_match($reg,$row[0])) {
			mysql_query("ALTER TABLE " . $row[0] . " CONVERT TO CHARACTER SET $set COLLATE $collation");
			echo $row[0] . " converted<br />";
		}

	}
}

// --------------------------------
// usage examples:
fix_encoding ("/^quiz/i"); // fix encoding only on tables that start with "quiz", it set UTF8 and utf8_general_ci
fix_encoding (); // fix all tables
fix_encoding ("/^quiz/i", "latin1", "latin1_general_ci"); // specifying also set and collation
// --------------------------------
Share

Tags: , , , ,

Nov 09 2009

Stop UTF8 problems with special characters

Category: MySql,Php — Giulio Pons @ 4:04 pm

I’m italian and I always need to use special chars like è é à ù. The same need belongs to french developers, and many other non-english developers. To use special chars without encoding problems try to follow this 4 rules:

  1. create fields on db with utf8_general_ci collation
  2. make the query ‘set names’ after any connection (look the block code below, you need mysql 5)
  3. put meta charset utf8 tag in the html head block
  4. read and write strings to db and to html without using utf8_encode or other functions
  //after connecting to your db make this query:
  mysql_query("SET NAMES 'utf8';");

NOTE (added on 2010-02-16)
Sometimes these steps do not work, and you start say f**ck f**ck! Well, it’s also happened to me, and I’ve notice that sometimes php.ini make some problems because it has a wrong default charset setted. So, if this is your problem, put this line at the beginning of your php files:

ini_set('default_charset', 'UTF-8');
Share

Tags: , , ,