Bravo List
Register
Go Back   > Bravo List > P2P > Forum > Tutorials
Reply
  #1  
Old 16-01-10, 13:26
Fynnon's Avatar
Fynnon Fynnon is offline
xxx
 
Join Date: Nov 2007
P2P
Posts: 963
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, 40 views)
Reply With Quote
The Following 2 Users Say Thank You to Fynnon For This Useful Post:
FENIX (21-02-13), Moh.ElBaz (03-02-10)
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 YSE 11 17-03-10 21:56
Content Encoding Error daffy phpMyBitTorrent (BT.Manager) 1 18-02-10 05:27
change encoding mondolfo Template Shares 6 30-11-09 06:35
Encoding devil007 Community Cafe 1 18-06-08 01:25



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