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…

February 11, 2010

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

Author

I'm a software engineer, an everyday web developer and a maker. I usually build sites with PHP, within or without WordPress. I build Internet of Things with Arduino and ESP8266. I'm the founder of Rockit.it and Dailybest.it and I'm actually the Chief Technical Officer of Better Days web agency.

Comments on “PHP function to fix collation on database fields of MySQL”

One thought

  1. Duglas Lima says:

    Very nice & interesting site. Thank you for share with us.

Comments are closed

Recommended

Stop UTF8 problems with special characters

I’m italian and I always need to use special chars like è é à ù. The same need belongs to…

November 9, 2009

MYSQL add counter in a query

Use mysql variables to create a counter in SQL, PHP code to use an SQL counter and what does the i mean in mysqli?

November 9, 2019

Modify the language attribute based on category in WordPress

How to modify the language attribute in your Wordpress theme using a specific value

November 7, 2019

Embedding images in HTML or CSS with PHP

This small function returns an encoded string to embed images, inline, inside your html/css code and reduce the number of…

December 4, 2013

MySQL fulltext search always empty

If you’ve just switched to FULLTEXT indexes and you’ve just started playing with MATCH... AGAINST syntax, but you always get…

November 23, 2013

How to remove custom fields from WordPress

Ok. You’ve added custom fields in wordpress, but how can you remove them? How to delete custom fields wordpress Two…

September 29, 2013