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

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

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

Stop sending email to new user in WordPress admin registration

Prevent WordPress to send notification emails when you manually create new users

March 9, 2017

Add Internet Explorer class to body to detect old browser

When you have clients that still uses an old browser, you need to handle it. This javascript function detects the…

February 27, 2017

Add filter on wp_title not working with Yoast SEO plugin

For SEO purposes, in a specific template that has a list of items with pagination, I need to have different…

February 22, 2017

Generate all old PDF preview images with WordPress 4.7

WordPress 4.7 generates thumbnails for PDF files while uploading them in the media library, this is a really good thing…

December 13, 2016