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

Anything to say?

Comments with Facebook or below with WordPress

WordPress comments:

Sorry, WordPress comments are closed on old posts.