Barattalo

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

Related posts:

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. PHP to get enum/set values from mysql field
5. MYSQL add counter in a query

Tags: char set, charcter set, collation, MySql, Utf8