Tag: sql

I cant count the times i had to debug a script for errors and found myself spending hours on it just to find out that it was a database collation / encoding issue. Living in a greek speaking country I find my self in such a situation quite frequently…
Here is a php script to convert all your database fields and tables to utf8 or any other encoding you may wish!

set_time_limit(900); /* or whatever your server can handle:)*/

function mysqlError()
{
    if (mysql_errno()) {
        echo "Mysql Error: " . mysql_error() . "\n";
    }
}

$username = "db_username";
$password = "sb_password";
$db = "databasename";
$host = "localhost";

$target_charset = "utf8";
$target_collate = "utf8_general_ci";

echo "Starting
";
$conn = mysql_connect($host, $username, $password);
mysql_select_db($db, $conn);
/*Select all the tables*/
$tables = array();
$r = mysql_query("SHOW TABLES");
mysqlError();
while (($row = mysql_fetch_row($r)) != null) {
    $tables[] = $row[0];
}

/* Begin processing */
foreach ($tables as $table) {
    $r = mysql_query("show index from {$table}");
    mysqlError();
    $indices = array();

    while (($row = mysql_fetch_array($r)) != null) {
        if ($row[2] != "PRIMARY") {
            $indices[] = array("name" =& gt; $row[2], "unique" =& gt; !($row[1] == "1"), "col" =& gt; $row[4]);
mysql_query("ALTER TABLE {$table} DROP INDEX {$row[2]}");
mysqlError();
echo "Dropped index {$row[2]}. Unique: {$row[1]}\n";
}
    }

    $res = mysql_query("DESCRIBE {$table}");
    mysqlError();
    while (($row = mysql_fetch_array($r)) != null) {
        $name = $row[0];
        $type = $row[1];
        $set = false;
        if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat)) {
            $size = $mat[1];
            mysql_query("ALTER TABLE {$table} MODIFY {$name} VARBINARY({$size})");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} VARCHAR({$size}) CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "CHAR")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} BINARY(1)");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} VARCHAR(1) CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "TINYTEXT")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} TINYBLOB");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} TINYTEXT CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "MEDIUMTEXT")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} MEDIUMBLOB");
            mysqlError();
            mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMTEXT CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "LONGTEXT")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} LONGBLOB");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} LONGTEXT CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        } else if (!strcasecmp($type, "TEXT")) {
            mysql_query("ALTER TABLE {$table} MODIFY {$name} BLOB");
            mysqlError();
            mysql_query("ALTER TABLE {$table} MODIFY {$name} TEXT CHARACTER SET {$target_charset}");
            mysqlError();
            $set = true;

            echo "Altered field {$name} on {$table} from type {$type}\n";
        }

        if ($set)
            mysql_query("ALTER TABLE {$table} MODIFY {$name} COLLATE {$target_collate}");
    }

    /* re-build indices as dropped in previous step*/
    foreach ($indices as $index) {
        if ($index["unique"]) {
            mysql_query("CREATE UNIQUE INDEX {$index["name"]} ON {$table} ({$index["col"]})");
            mysqlError();
        } else {
            mysql_query("CREATE INDEX {$index["name"]} ON {$table} ({$index["col"]})");
            mysqlError();
        }

        echo "Created index {$index["name"]} on {$table}. Unique: {$index["unique"]}\n";
    }

    /* set default collate on each table*/
    mysql_query("ALTER TABLE {$table}  DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
}
/* change database charset*/
mysql_query("ALTER DATABASE {$db} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
mysql_close($conn);

And thats it… Make sure you change the database variables (Username password dbname)!!