Function to set COLLATION on database fields of Mysql

February 3rd, 2013 by Laeeq | No comments

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. ?>

You can subscribe to PHPZAG.COM posts by Email

 

Related Topics:

  • How much memory do PHP variables use?
  • Manipulating PHP arrays: push, pop, shift, unshift
  • 10 Super Useful PHP Code Snippets
  • Let’s play with php.ini file Configuration
  • 15+ regular expressions for PHP developers
  •  

     

    1. No comments yet.
    1. No trackbacks yet.