A PHP script for converting character set for all tables in a database

Recently I migrated a drupal site that uses an ms SQL server to MySQL.

One of the conversions required me to convert all the tables from latin1_swedish_ci to utf8_general_ci character set.

If you only want to convert a few tables, just execute the following SQL statement.

ALTER TABLE tableName CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

We all know a typical drupal site can have100+ tables.

In my case, the database I needed to convert had 350+ tables, so doing it manually was simply not an option.

So I am sharing a simple script I used to do all the conversions.

 

// Change the following setting as needed.
$host = "localhost";
$db_name = 'dbname';
$db_username = "root";
$db_password = "root";
$convert_to = "utf8_general_ci";

try {
 $conn = new PDO("mysql:host=$host;dbname=$db_name", $db_username, $db_password);
 // set the PDO error mode to exception
 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 $sql = "SHOW TABLES";

 print "Following SQL Statements have been executed. 

";

 foreach ($conn->query($sql) as $row) {
  	 $table_name = $row['Tables_in_' . $db_name];
  	 $sql = 'ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET utf8 COLLATE ' . $convert_to;
  	 $output = $sql . '
';

  	 print $output;
  	 $conn->query($sql);
 }

}
catch(PDOException $e)
{
 echo "Connection failed: " . $e->getMessage();
}

Good luck and I hope this helps! Download the script using the link below.

Download Code

 

Need help with a Drupal project? The Drupal Development team at Cheeky Monkey does a lot of backend work with other agencies. If you’ve taken on a project that can use some extra hands, give us a call!