dr_lucas Posted January 30, 2016 Share Posted January 30, 2016 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 :) Did I help you? Click "Like" or "Thanks"! It's free of charge. :)My contributions:Total Configuration (newly updated 07/2018, for both osC 2.2 and 2.3.4.1 BS Frozen CE)User Tracking with Admin 1.0 (newly updated 07/2018)FedEx - Web Services v9, FAQ System , Who's Online Enhancement, Order Editor, MoneyBookers IPN, Ship in Cart (MS2), Admin Products Paging, Margin Report v1.00, 2Checkout INS / IPN (Instant Notification System) for MS2.2, Visitor Web Stats, Time Zone Offset - Adjust to match your location, Category Meta Tags Link to comment Share on other sites More sharing options...
dr_lucas Posted January 31, 2016 Author Share Posted January 31, 2016 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!"; ?> Did I help you? Click "Like" or "Thanks"! It's free of charge. :)My contributions:Total Configuration (newly updated 07/2018, for both osC 2.2 and 2.3.4.1 BS Frozen CE)User Tracking with Admin 1.0 (newly updated 07/2018)FedEx - Web Services v9, FAQ System , Who's Online Enhancement, Order Editor, MoneyBookers IPN, Ship in Cart (MS2), Admin Products Paging, Margin Report v1.00, 2Checkout INS / IPN (Instant Notification System) for MS2.2, Visitor Web Stats, Time Zone Offset - Adjust to match your location, Category Meta Tags Link to comment Share on other sites More sharing options...
burt Posted January 31, 2016 Share Posted January 31, 2016 Note that there is an inbuilt way to do this since (if I recall correctly) 2.3.3.3 Admin > Tools > Database Tables Link to comment Share on other sites More sharing options...
dr_lucas Posted January 31, 2016 Author Share Posted January 31, 2016 Yes, @@burt, that is correct. This is meant especially for older osC versions, like mine (on an extremely modified 2.2 RC2a base). Did I help you? Click "Like" or "Thanks"! It's free of charge. :)My contributions:Total Configuration (newly updated 07/2018, for both osC 2.2 and 2.3.4.1 BS Frozen CE)User Tracking with Admin 1.0 (newly updated 07/2018)FedEx - Web Services v9, FAQ System , Who's Online Enhancement, Order Editor, MoneyBookers IPN, Ship in Cart (MS2), Admin Products Paging, Margin Report v1.00, 2Checkout INS / IPN (Instant Notification System) for MS2.2, Visitor Web Stats, Time Zone Offset - Adjust to match your location, Category Meta Tags Link to comment Share on other sites More sharing options...
dr_lucas Posted January 31, 2016 Author Share Posted January 31, 2016 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. Did I help you? Click "Like" or "Thanks"! It's free of charge. :)My contributions:Total Configuration (newly updated 07/2018, for both osC 2.2 and 2.3.4.1 BS Frozen CE)User Tracking with Admin 1.0 (newly updated 07/2018)FedEx - Web Services v9, FAQ System , Who's Online Enhancement, Order Editor, MoneyBookers IPN, Ship in Cart (MS2), Admin Products Paging, Margin Report v1.00, 2Checkout INS / IPN (Instant Notification System) for MS2.2, Visitor Web Stats, Time Zone Offset - Adjust to match your location, Category Meta Tags Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.