Bravo List
Register
Go Back   > Bravo List > P2P > Forum > Tutorials
Reply
  #1  
Old 16th January 2010, 13:26
Fynnon's Avatar
Fynnon Fynnon is offline
xxx
 
Join Date: Nov 2007
P2P
Posts: 984
Default Convert MySQL database encoding
Convert MySQL database from one encoding/collation into another

PHP Code:
<?php
// original script (v1.0) by/from: http://www.phpwact.org/php/i18n/utf-8/mysql
// improved/modified (v1.04) by Bogdan http://bogdan.org.ua/
// this script will output all queries needed to change all fields/tables to a different collation
// it is HIGHLY suggested you take a MySQL dump/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   'utf8_general_ci';
// character set of new collation:
$character_set'utf8';
// DB login information - *modify before use*
$username 'user';
$password 'pass';
$database 'database_name';
$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 '';
        
// Alter field collation:
        // ALTER TABLE `tab` CHANGE `fiel` `fiel` 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";
        }
    }
}
?>
You can play with DBs here: phpMyAdmin 3.4.0-dev - demo.phpmyadmin.net
Attached Files
File Type: php UTF.php (2.3 KB, 44 views)
Reply With Quote
The Following 2 Users Say Thank You to Fynnon For This Useful Post:
FENIX (21st February 2013), Moh.ElBaz (3rd February 2010)
Reply

Tags
convert , database , encoding , mysql

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Encoding problem cekosexama Yuna Scatari Edition (YSE) 11 17th March 2010 21:56
Content Encoding Error daffy BT.Manager (phpMyBitTorrent) 1 18th February 2010 05:27
change encoding mondolfo Template Shares 6 30th November 2009 06:35
Encoding devil007 Community Cafe 1 18th June 2008 01:25



All times are GMT +2. The time now is 10:13. vBulletin skin by ForumMonkeys. Powered by vBulletin® Version 3.8.11 Beta 3
Copyright ©2000 - 2024, vBulletin Solutions Inc.