PHPBuilder - ODBC (Access) -> MySQL Conversion



RSS Twitter
Snippets Databases

ODBC (Access) -> MySQL Conversion

by: Jason Farrell
|
May 5, 2003

Version: 1.2

Type: Full Script

Category: Databases

License: GNU General Public License

Description: Running this script will take the selected ODBC datasource and place all the data into a MySQL database. Please be sure to have the MySQL database setup and ready before running this script



<?php
    /*
        This script was written by Jason Farrell on this 4th day of April in 2003.
        This code is solely for educational purposes and may not be redistributed
        without the consent of the author. Any uses and/or responsibilites, outside
        the permission of the author are not binding to the author in any way.
        
        Please email comments to jfarrell@stu.wccnet.edu
    */
    $odbc=odbc_connect('my_dsn','my_uname','my_pass');  //Open Connection to Datasource
    
    $mysql=mysql_connect('my_host','my_uname','my_pass'); //Open Connection to MySQL and Select DB
    mysql_select_db('my_db',$mysql);
    
    $tablelist=odbc_tables($odbc);              //This will create a result set of the available tables for the datasource
    while (odbc_fetch_into($tablelist,$r))      //This will fetch each row into a hash $r
    {
        if (strtolower($r[3])=='table')         //This is a regular table and is not tabular information
        {   
            $q="select * from {$r[2]}";
            $odbcset=odbc_exec($odbc,$q);
            
            //Begin build sql insert command
            while (odbc_fetch_into($odbcset,$row))      //Create a hash $row from the query to select all the data from the listed table
            {
                $c="insert into {$r[2]}(".odbc_field_name($odbcset,1);  //Build Intial Insert Statement for data insertion into mysql
                for ($i=2; $i<=odbc_num_fields($odbcset); $i++)         //Loop in remainder of field names
                {
                    $c .= ",".odbc_field_name($odbcset,$i);
                }
                if (is_numeric($row[0])) {      //Build Initial Value parameters values 
                    $c.=") values(".$row[0];
                } else {
                    $c.=") values('".$row[0]."'";
                }
                for ($i=1; $i<count($row); $i++)        //Loop in remainder
                {
                    if (is_numeric($row[$i])) {
                        $c .= ",".$row[$i];
                    } else {
                        $c .= ",'".$row[$i]."'";
                    }
                }
                $c.=")";
                #echo $c."<br>\n";
                if (mysql_query($c,$mysql)) {   //Insert the Data into the table 
                    echo "Success<br>\n";       //Echo Success if it worked
                } else {
                    die(mysql_error());         //IF it fails die
                }
            }
        }
    }
    mysql_close($mysql);        //Close all open connections
    odbc_close($odbc);
?>

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Jason Farrell

Comment:



Comment:

(Maximum characters: 1200). You have characters left.