MySQL Collate fix

fix_collate.php

<?php
// Stolen from: http://www.holisticsystems.co.uk/blog/?p=931
// Modified to work using mysqli

$server = 'localhost';
$username = 'root';
$password = 'root';
$database = 'ojs';
$new_charset = 'utf8';
$new_collation = 'utf8_unicode_ci';

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

// Change database collation
mysqli_query($db, "ALTER DATABASE $database DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");

// Loop through all tables changing collation
$result=mysqli_query($db, 'show tables');
while($tables = mysqli_fetch_array($result)) {
    $table = $tables[0];
    mysqli_query($db, "ALTER TABLE $table DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

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

    print "changed collation of $table to $new_collation\n";
}
print "\n\nThe collation of your database has been successfully changed!";