Database Charset and Collation Changing

Here is a useful PHP script for changing collation of your database.

Just follow the steps:

1. Create file e.g.: changedbcollation.php with the following content:

<?php

/**
* Script for changing charset and collation of DB
*
* @author   Lone Shooter
*/
// Configuration - CHANGE PARAMETERS!!!
$server = 'localhost';
$username = 'your_db_username';
$password = 'your_db_password';
$database = 'your_db_name';
$new_charset = 'utf8';
$new_collation = 'utf8_unicode_ci';

// Connect to database
$db = mysql_connect($server, $username, $password);
if(!$db) die("Cannot connect to database server -".mysql_error());
$select_db = mysql_select_db($database);
if (!$select_db) die("could not select $database: ".mysql_error());

// Change database collation
mysql_query("ALTER DATABASE $database DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");

// Loop through all tables changing collation
$result=mysql_query('SHOW TABLES');
while($tables = mysql_fetch_array($result)) {
	$table = $tables[0];
	mysql_query("ALTER TABLE $table DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

	// Loop through each column changing collation
	$columns = mysql_query("SHOW FULL COLUMNS FROM $table where collation is not null");
	while($cols = mysql_fetch_array($columns)) {
		$column = $cols[0];
		$type = $cols[1];
		mysql_query("ALTER TABLE $table MODIFY $column $type CHARACTER SET $new_charset COLLATE $new_collation");
	}

	print "Changed collation of $table to $new_collation<br/>";
}

print '<br/>The collation of database has been successfully changed!<br/>';

?>

2. Upload the file in some of your server’s folder and access it via URL

All tables in the database, together with their fields, will be updated with new encoding and collation.