Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Script converting the entire database to UTF-8 (including tables and columns)


dr_lucas

Recommended Posts

I just upgraded my server to PHP 5.6 and bumped into many issues with UTF-8.

This little script resolved almost all of them, so I hope it will help others that need to convert their entire DB to UTF-8 on the fly.

Usage: Simply copy and paste this code into a file, drop it in your catalog root directory and execute it.

 

DISCLAIMER: Be sure to backup all your data before executing it. Although it works perfectly fine on every MySQL database I tested it, I will not be held responsible for any damage caused by this script or your usage of it. BACKUP, BACKUP, BACKUP!

 

 

<?php
require 'includes/configure.php'; //get the database configuration
 
$con = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
if (!$con) {
    echo "Cannot connect to the database ";
    die();
}
mysql_select_db(DB_DATABASE);
$result = mysql_query('show tables');
while ($tables = mysql_fetch_array($result)) {
    foreach ($tables as $key => $value) {
        mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
    }
}
echo "The collation of your database has been successfully changed!";

 

?>

 

 

Hope this helps :)

Link to comment
Share on other sites

If anybody prefers utf_unicode_ci instead of utf8_general_ci, please use this code instead:
 

<?php
require 'includes/configure.php'; //get the database configuration
 
$con = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
if (!$con) {
    echo "Cannot connect to the database ";
    die();
}
mysql_select_db(DB_DATABASE);
$result = mysql_query('show tables');
while ($tables = mysql_fetch_array($result)) {
    foreach ($tables as $key => $value) {
        mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
    }
}
echo "The collation of your database has been successfully changed!";

 

?>
Link to comment
Share on other sites

Yes, @@burt, that is correct.

This is meant especially for older osC versions, like mine (on an extremely modified 2.2 RC2a base).

Link to comment
Share on other sites

This is the mysqli version, for anyone using php 7:

 

<?php
require 'includes/configure.php'; //get the database configuration

$con = mysqli_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD,DB_DATABASE);
if (!$con) {
    echo "Cannot connect to the database ";
    die();
}

 $tables = array_column(mysqli_fetch_all($con->query('SHOW TABLES')),0);


    foreach ($tables as $key => $value) {
        mysqli_query($con, "ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
        echo "ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci";
    }

echo "The collation of your database has been successfully changed!";
?>

You may change utf8_unicode_ci to utf8_general_ci or any other encoding if you prefer, although I believe unicode is better.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...