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.
- <?php
- function setEncoding($regex=‘//’,$set=‘utf8′,$collation=‘utf8_general_ci’) {
- $result = mysql_query(“SHOW TABLES”);
- while ($res_row = mysql_fetch_row($result)) {
- if (preg_match($regex,$res_row[0])) {
- mysql_query(“ALTER TABLE ” . $res_row[0] . “ CONVERT TO CHARACTER SET $set COLLATE $collation”);
- echo “Converted:”.$res_row[0] . ”
- “;
- }
- }
- }
- ?>
Above function usage example:
- <?php
- // set encoding on tables that start with ”employee”, it set UTF8 and utf8_general_ci
- setEncoding (“/^employee/i”);
- // set encoding on all tables
- setEncoding ();
- // set encoding on tables with specified set and collation
- setEncoding (“/^employee/i”, “latin1″, “latin1_general_ci”);
- ?>
Follow @phpzag
