PHP function to fix collation on database fields of MySQL

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

  1. Stop UTF8 problems with special characters
  2. 10 PHP usefull functions for MySQLi stuff (mysql improved)
  3. 10 PHP usefull functions for MySQL stuff
  4. 13 mysql tips that you don’t know
  5. PHP to get enum/set values from mysql field

One comment

Post a comment

You may use the following HTML:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>