Skip to main content

The Cheeky Monkey Media Blog

A few words from the apes, monkeys, and various primates that make up the Cheeky Monkey Super Squad.

converting character set for all tables in a database

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

Recently I migrated a drupal site that uses 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.

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


  1. // Change the following setting as needed.
  2. $host = "localhost";
  3. $db_name = 'dbname';
  4. $db_username = "root";
  5. $db_password = "root";
  6. $convert_to = "utf8_general_ci";
  8. try {
  9.  $conn = new PDO("mysql:host=$host;dbname=$db_name", $db_username, $db_password);
  10.  // set the PDO error mode to exception
  11.  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  13.  $sql = "SHOW TABLES";
  15.  print "Following SQL Statements have been executed. 
  17. ";
  19.  foreach ($conn->query($sql) as $row) {
  20.   	 $table_name = $row['Tables_in_' . $db_name];
  21.   	 $sql = 'ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET utf8 COLLATE ' . $convert_to;
  22.   	 $output = $sql . '
  23. ';
  25.   	 print $output;
  26.   	 $conn->query($sql);
  27.  }
  29. }
  30. catch(PDOException $e)
  31. {
  32.  echo "Connection failed: " . $e->getMessage();
  33. }

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!

Web Development

Would you like to know more about what we do?

View Our Services

Graphic Design Cheeky Monkey

Have a look at some our client work

View Our Work

Cheeky Monkey Discovery

Learn more about Cheeky Monkey Media

About Us