Convert ISO DB Data to UTF DB Data

This plugin will convert ISO-8859-1 data to UTF-8 data in a MySQL UTF-8 WordPress database where things at some stage went wrong - this can happen during a command line backup/restore batch if your shell is misconfigured, for instance.

The script will first, when you push the button below, scan your WordPress database for applicable tables and columns. You will then have the choice to exclude any column you feel shouldn't be converted.

Once you've checked your exclusions (if any) simply hit the next button to perform the conversion. Obviously, depending on database size, this my take a while.

You need to ensure your PHP settings have sufficient execution time to perform the conversion.

This plugin requires MySQL Improved Extension (mysqli).

The following applicable tables and columns were found during the database scan.

Mark/select the checkboxes of the columns you do not want to convert, before continuing by hitting the button by the bottom of the list

 

Use at your own risk. Take DB backups before proceeding.

 

All done! Clear all cache instances and reload your site. That's it really.

 

This plugin was used at your own risk.

 

$pv) { $test = explode('---', $pk); if($test[1]) { $exclude[$test[0]][$test[1]] = 1; } } } //enable the DB scan form button on initial load if (!$_POST['check_for_iso_to_utf']) { ?>

query ("SHOW TABLES")) { while ($tables = $res_tables->fetch_array(MYSQLI_NUM) ) { //second, get all columns in the tables if ($res_fields = $DB->query ("SHOW COLUMNS FROM ".$tables[0])) { //fetch unique columns from the table if ($res_key = $DB->query ("SHOW COLUMNS FROM ".$tables[0]." WHERE `Key` LIKE 'PRI'")) { $key = $res_key->fetch_assoc(); $unique_key = $key['Field']; } //only display exclude form on database scan if (!$_POST['do_iso_to_utf']) { ?> fetch_array(MYSQLI_ASSOC) ) { //only operate on columns that should be converted -and- //that have primary, unique columns if (in_array($fields['Type'], $field_types) && isset($unique_key)) { //cheap clean-up flag for table/column view $data_is_set=true; //only display exclude form on database scan if (!$_POST['do_iso_to_utf']) { ?> query("SET NAMES latin1"); if ($res = $DB->query ("SELECT ".$unique_key.", ".$fields['Field']." FROM ".$tables[0]." WHERE 1=1")) { while ($data = $res->fetch_object() ) { //magic 2: insert data in UTF $DB->query("SET NAMES utf8;"); $unique_field = $data->$unique_key; //magic 3: treat data has binary and convert it to HEX //so PHP and system won't fiddle with it $fix_field = bin2hex($data->$fields['Field']); //magic 4: ensure we're not running this on anything that would be //considered ISO-8859-1, which in reality is UTF-8 (due to conversion magic) //as MySQL update then would chop off the string $darr[] = "UTF-8"; $darr[] = "ISO-8859-1"; if (mb_detect_encoding($data->$fields['Field'], $darr) != 'UTF-8') { echo "

---

"; echo "Table: ".$tables[0]."
"; echo "Column: ".$fields['Field']."
"; echo "Content: ".$data->$fields['Field']."

"; echo "Was not processed as it contains UTF-8 specific characters"; echo "

---

"; } else { //magic 5: let MySQL directly un-HEX the data $result = $DB->query (" UPDATE ".$tables[0]." SET ".$fields['Field']." = UNHEX('".$DB->real_escape_string($fix_field)."') WHERE ".$unique_key." = '".$unique_field."' "); } //we're looping - better safe than donk unset($unique_field); unset($fix_field); unset($darr); } } } } } //if cheap clean-up flag is set, display alternative message if(!isset($data_is_set)) { //only display exclude form on database scan if (!$_POST['do_iso_to_utf']) { ?>

	Exclude
No columns to convert found in this table