Site maintenance: Tuesday 17 Nov, from 10:00 UTC, about 4–6 hours. Details

This cutover relies on some testing that is still incomplete so may be cancelled at short notice

Forums / General Questions / Converting database back to latin1

Converting database back to latin1

Started by lat9 on 7 Apr 2012, 12:42 PM · Views: 600

Results 1 to 3 of 3
7 Apr 2012, 12:42 PM
#1
lat9 avatar

lat9

Administrator

Join Date:
Sep 2009
Location:
Stuart, FL
Posts:
14,139
Plugin Contributions:
56

Converting database back to latin1

When I upgraded to v1.5.0, I made the mistake of specifying the UTF8 character set for my store/database. I found this link that worked for me.

The code provided created the list of SQL statements that were necessary to convert my database's data from one encoding to another, which I then ran through phpMyAdmin.

I'm all good, back to latin1!

12 Apr 2012, 1:24 PM
#2
bwtharp avatar

bwtharp

New Zenner

Join Date:
Nov 2009
Location:
Mountain Maryland
Posts:
10
Plugin Contributions:
0

Re: Converting database back to latin1

your link does not seem to work

12 Apr 2012, 1:30 PM
#3
lat9 avatar

lat9

Administrator

Join Date:
Sep 2009
Location:
Stuart, FL
Posts:
14,139
Plugin Contributions:
56

Re: Converting database back to latin1

You're right, the link seems to be busted. Here's a copy of the file that is referenced on that site:

<?php
// Original script (v1.0) by/from: http://www.phpwact.org/php/i18n/utf-8/mysql
// Improved/modified (v1.05) by Bogdan http://bogdan.org.ua/
// Last updated: 2010-06-28
// this script will output all queries needed to change all fields/tables to a different collation
// it is HIGHLY suggested you make a MySQL backup prior to running any of the generated queries
// this code is provided AS IS and without any warranty
// add text/plain header when used not as a CLI script; PHP CLI SAPI shouldn't output headers
header("Content-Type: text/plain");
// precaution
die("Make a backup of your MySQL database, then remove this line from the code!");
set_time_limit(0);
// collation you want to change to:
$convert_to   = 'latin1_general_ci';
// character set of new collation:
$character_set= 'latin1';
// DB login information - *modify before use*
$username = 'root';
$password = '';
$database = '';
$host  = 'localhost';
//-- usually, there is nothing to modify below this line --//
// show TABLE alteration queries?
$show_alter_table = true;
// show FIELD alteration queries?
$show_alter_field = true;
mysql_connect($host, $username, $password);
mysql_select_db($database);
$rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());
while ($row_tables = mysql_fetch_row($rs_tables)) {
 $table = mysql_real_escape_string($row_tables[0]);
 // Alter table collation
 // ALTER TABLE `account` DEFAULT CHARACTER SET utf8
 if ($show_alter_table)
  echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\n");
 $rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());
 while ( $row = mysql_fetch_assoc($rs) ) {
  if ( $row['Collation'] == '' || $row['Collation'] == $convert_to )
   continue;
  // Is the field allowed to be null?
  if ( $row['Null'] == 'YES' )
   $nullable = ' NULL ';
  else
   $nullable = ' NOT NULL ';
  // Does the field default to null, a string, or nothing?
  if ( $row['Default'] === NULL && $row['Null'] == 'YES' )
   $default = " DEFAULT NULL ";
  elseif ( $row['Default'] != '' )
   $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
  else
   $default = '';
  // sanity check and fix:
  if ($nullable == ' NOT NULL ' && $default == ' DEFAULT NULL ') {
   $default = '';
   echo "/* Warning: wrong combination of 'default value' and 'NULL-flag' detected - and fixed! */\n";
   echo "/* Diagnostics: row[Null] = '$row[Null]', row[Default] = " . mysql_real_escape_string($row['Default']) . ", MySQL version: " . mysql_get_server_info() . " */\n";
  }
  // Don't alter INT columns: no collations, and altering them drops autoincrement values
  if (strpos($row['Type'], 'int') !== false) {
   $show_alter_field = False;
  }
  else {
   $show_alter_field = True;
  }
  // Alter field collation:
  // ALTER TABLE `tab` CHANGE `field` `field` CHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
  if ($show_alter_field) {
   $field = mysql_real_escape_string($row['Field']);
   echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default;\n";
  }
 }
}
?>