Function to set COLLATION on database fields of Mysql

As a PHP developer, we sometimes need PHP functions or script that can change the default collation in all tables and fields in a database. Actually we often forget to set collation when create tables and fields in Mysql database. Here we have created a complete PHP function to search for tables in the active database, match table’s name with the regular expression passed as first parameters and if it matches alter the table to convert character set and collation. It can be useful when you have a database that you’ve created without setting collation.

  1. <?php
  2. function setEncoding($regex=‘//’,$set=‘utf8’,$collation=‘utf8_general_ci’) {
  3. $result = mysql_query(“SHOW TABLES”);
  4. while ($res_row = mysql_fetch_row($result)) {
  5. if (preg_match($regex,$res_row[0])) {
  6. mysql_query(“ALTER TABLE “ . $res_row[0] . ” CONVERT TO CHARACTER SET $set COLLATE $collation”);
  7. echo “Converted:”.$res_row[0] . ”
  8. “;
  9. }
  10. }
  11. }
  12. ?>

Above function usage example:

  1. <?php
  2. // set encoding on tables that start with “employee”, it set UTF8 and utf8_general_ci
  3. setEncoding (“/^employee/i”);
  4. // set encoding on all tables
  5. setEncoding ();
  6. // set encoding on tables with specified set and collation
  7. setEncoding (“/^employee/i”, “latin1”, “latin1_general_ci”);
  8. ?>

Laeeq

A web developer who has a love for creativity and enjoys experimenting with various techniques in both web design and development. If you would like to be kept up to date with our posts, you can follow us on Twitter, or even by subscribing to our RSS Feed.

Leave a Reply

Your email address will not be published. Required fields are marked *

WordPress SEO fine-tune by Meta SEO Pack from Poradnik Webmastera