太阳帆数据 Sunfine Data

The Problem
Recently I solved a database migration problem: migrate MS Access database encoded in GB2312 to MySQL server encoded in UTF-8. I’d like to share what I did and hope it is useful to help you solve your problem.

I had a lot of useful data in an MS Access database. They were collected through ASP Gallery 1.0, which was run on Windows IIS web server. I did not have an IIS server to run ASP Gallery for a long time and do not have a plan to launch an IIS server in the coming future.

I wrote simple PERL CGI’s and used it from 2007 to 2008. Now I have a powerful Apache2 server and I developed an powerful PHP web application. In order to use the useful information in the MS Access database, I have to migrate all data to MySQL server.

The Solution
There is a big problem in front of me. All data in the MS Access database was encoded in GB2312. My MySQL server and everything else related to the new web server use UTF-8 encoding. I have to convert the GB2312 encoding to UTF-8 encoding. That was solved easily. See the corresponding function in migrate.php.

First I used the MySQL migrate tool (you can download it from mysql.com website) to transfer data (migrate_class) to MySQL server. Then I wrote a nice PHP application called migrate.php, that help me to merge the data from the table to MySQL server (category) table in donghome database.

The PHP code

<?php
// database-specific information
DEFINE(DB_USER,"your username");
DEFINE(DB_PASSWORD,"your password");
DEFINE(DB_HOST,"your mysql server host");
DEFINE(DB_NAME,"gmain");

// connect to mysql
$db_connection = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);

// select the database
mysql_select_db(DB_NAME);

// process gmain database that includes old gallery data tables

//update_class_table();

//migrate_class();

function migrate_class()
{
	$sql = "SELECT classid, pclassid, title FROM class";
	$query_result = mysql_query($sql);
	$counter = 0;
	while ($row = mysql_fetch_array($query_result, MYSQL_NUM))
	{
		$classid=$row[0];
		$pClassID = $row[1];
		$name = $row[2];
		$counter ++;
		echo $counter. " : ";
		update_category($classid, $pClassID, $name);
	}

}
function update_category($classid, $pClassID, $name)
{
	$sql1 = "INSERT INTO donghome.category (id_category,parent_id,name) ".
			"VALUES ($classid,$pClassID,'".mysql_escape_string($name)."');";
	echo $sql1."<br>";
	mysql_query($sql1);
}

function update_class_table()
{
	$sql = "SELECT * FROM migrate_class";
	$query_result = mysql_query($sql);
	$str = "all classes in the table <br>";
	$str .= "ClassID,directory,pClassID,title,hyperlink,hasdir,user,path<br>";
	while ($row = mysql_fetch_array($query_result, MYSQL_NUM))
	{
		$path = "";
		$classid=$row[0];
		$directory = $row[1];
		$pClassID = $row[2];
		$title = $row[3];
		$hyperlink = $row[4];
		$hasdir = $row[5];
		if ($row[5])
		{
			$path = buildpath($directory,$pClassID);
		}
		$str .= "*** $row[0],$row[1],$row[2],".convertGB_UTF8($title).",$row[4],$row[5],$row[6],$path<br>";
		updateClass($classid,$directory,$pClassID,$title, $hyperlink, $hasdir, $path);
	}
	echo $str;
}

function updateClass($classid,$directory,$pClassID,$title, $hyperlink, $hasdir, $path)
{
	$sql1 = "INSERT INTO class (classid,directory,pclassid,title,hyperlink,hasdir,path) ".
			"VALUES ($classid,'$directory',$pClassID,'".mysql_escape_string(convertGB_UTF8($title))."', '$hyperlink', $hasdir, '$path');";
	echo $sql1."<br>";
	mysql_query($sql1);
}

function buildpath($path,$parentdir)
{
	$apath = $path;
	$sql = "SELECT * FROM migrate_class WHERE ClassId = $parentdir;";
	$query_result = mysql_query($sql);
	$row = mysql_fetch_array($query_result, MYSQL_NUM);
    if ($row[5])
	{
		$apath = $row[1]."/".$apath;
	}
    $aparentdir = $row[2];
	if ($aparentdir != 0)
	{
		$apath = buildpath($apath,$aparentdir);
	}
	return $apath;
}

function convertGB_UTF8($str)
{
	$str = mb_convert_encoding($str, "UTF-8", "GB2312");
	return $str;
}

?>