access|mysql| Program | data | Database Recently, I am in order to develop an information management system for the company from the previous trial run development machine (Windows NT + IIS4.0 + Access) Migrating to a real Linux server (Apache1.3.12 + PHP 4.03 + MySQL 3.23.26), where the contents of the dozens of tables in the database migrated, began to tip me some twists and starts to download some MySQL client software or database management software from the Internet. , write better software have data migration function, but its migration way is only two kinds, one is to adopt the file introduction way, this kind of method in processing the database has and delimiter the same character, will produce error, especially in the processing access in the Memo field, it is easy to error, The final result is that the data after the export is not much or less. And the other one to support the direct import of ODBC features strong, the basic can not mistakenly import the contents of each table, but unfortunately, this must be built in access to the table in the field is English, such as in Access in the field is the Chinese name, generally also error, can not successfully import.
For this I had to spend a little time to write two small programs, used to transfer the contents of the Access database to MySQL, after the use, the effect is good, special here to give you a try or judge.
Let's outline how to use it,
1, create a "system DSN" for access's database;
2, according to the name of each table in Access database, set up the corresponding empty table in MySQL;
3, running fdlist.php;
4, running import.php;
5, each run 3, 4 steps to migrate a table, and then modify the Access source table name in fdlist.php and the name of the target table in MySQL, then run 3, 4 steps until all the tables are migrated.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The following is the fdlist.php source program
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<style type=text/css>
BODY,TD,LI,DIV,P,PRE,A,B,H1,H2,H3,H4 {font-family:verdana;font-size:9pt;line-height:18px;color: #a00000}
</style>
?
$dbconnection = @mysql_connect ("Yourmysqlserver", "Mysqlaccount", "Mysqlpassword")
Or Die ("Can not connect to database server");
@mysql_select_db ("Yourdatabase")
Or Die ("<p style= ' font-size:9pt;font-family:verdana;color: #803333; Font-weight:bold ' >no database,</p>" ) ;
$odbc _table = "youroriginaltable"; The original table name in your ODBC database
$mysql _table = "yournewtable"; The new table name in your Mysql Database.
?>
<body bgcolor= #f0f0f0 topmargin=0 leftmargin=0 text= #a00000 >
<br>
<div style= "Font-size:24pt;font-family:times;font-weight:bold;color: #00a000" >fields List of Two tables</ Div>
<HR size=1 color= #900000 >
?
$conn = Odbc_connect ("task", "", "");
$odbc _query = "SELECT * from". $odbc _table. "Where 1=2";
$recordsid = Odbc_exec ($conn, $odbc _query);
$idcounts = Odbc_num_fields ($RECORDSID);
$fdlist 1 = "";
for ($i = 1; $i <= $idcounts; $i + +)
$fdlist 1. = Odbc_field_name ($recordsid, $i). ",";
echo "<div> Fd1 =". $fdlist 1;
$fdlist 1 = substr ($fdlist 1,0,strlen ($fdlist 1)-1);
$fdlist 2 = "";
$sqlquery = "SELECT * from". $mysql _table. "Where 1=2";
$records 2 = mysql_query ($sqlquery);
$idcount 2 = Mysql_num_fields ($records 2);
for ($i = 0; $i < $idcount 2; $i + +)
$fdlist 2. = Mysql_field_name ($records 2, $i). ",";
echo "<div> FD2 =". $fdlist 2;
$fdlist 2 = substr ($fdlist 2,0,strlen ($fdlist 2)-1);
$fp = fopen ("Fdlist.txt", "w");
Fwrite ($fp, $ctable);
Fwrite ($fp, "n");
Fwrite ($fp, $fdlist 1);
Fwrite ($fp, "n");
Fwrite ($fp, $etable);
Fwrite ($fp, "n");
Fwrite ($FP, $fdlist 2);
Fclose ($FP);
Odbc_close ($conn);
if ($idcount 2!= $idcounts) {
echo "
"<div style= ' Font-size:20pt;font-family:times;font-weight:bold ' > The fields of two tables doesn ' t match";
echo "<br><br>odbc_table Fields =". $idcounts;
echo "<br><br>mysql_table Fields =". $idcount 2;
}
?>
</body>
~~~~~~~~~~~~~~~~~~~
Not finished (ii)
~~~~~~~~~~~~~~~~~~~
