SpeedBird Posted February 26, 2007 Share Posted February 26, 2007 I'm having difficulty working out how to do a query/insert between databases. What I need to do is query all rows from a table in one database, and insert them into an (identical) blank table in another, separate database. This needs to be done on-the-fly rather than via an automated script or database utility. I'm sure I need to use foreach for this. Here's what I've got so far: <?php /* Open an ODBC connection to the first database: */ $odbc = odbc_connect('dsn','user','pass'); if (!$odbc) {exit("Connect failed: " . $odbc);} /* SQL query (results stored in array) */ $cust_query = "SELECT customerid, name, surname FROM tblcustomer ORDER BY customerid"; $cust_data[] = odbc_exec($odbc,$cust_query); if (!$cust_data) {exit("SQL error retrieving customer data!");} /* close odbc connection */ odbc_close($odbc); /* open mysql connection */ mysql_connect('127.0.0.1:3306', 'user', 'pass') or die('Could not connect: ' . mysql_error()); mysql_select_db('database'); ?> I know I need to do an INSERT...ON DUPLICATE KEY UPDATE but how do I loop it so that each row is entered as a new record? Quote Link to comment Share on other sites More sharing options...
SpeedBird Posted February 26, 2007 Author Share Posted February 26, 2007 *BUMP* In case I wasn't clear, the blank table already exists in the other database and doesn't need to be created. Using INSERT...ON DUPLICATE KEY UPDATE means that any new rows will be copied to the new table and existing rows (where an indexed id field exists) will be updated. My only problem is figuring out how to loop through each element in the array and insert the data as a new row. Quote Link to comment Share on other sites More sharing options...
craygo Posted February 26, 2007 Share Posted February 26, 2007 what type of database is the second table in?? LOL sorry didn't scroll down far enough <?php /* Open an ODBC connection to the first database: */ $odbc = odbc_connect('dsn','user','pass'); if (!$odbc) {exit("Connect failed: " . $odbc);} /* SQL query (results stored in array) */ $data = ''; $cust_query = "SELECT customerid, name, surname FROM tblcustomer ORDER BY customerid"; $res = odbc_exec($odbc,$cust_query); while($r = odbc_fetch_array($res)){ $data .= "('".$r['customerid']."', '".$r['name']."', '".$r['surname']."'), "; } $data = substr($data,0,-1); if (!$cust_data) {exit("SQL error retrieving customer data!");} /* close odbc connection */ odbc_close($odbc); /* open mysql connection */ mysql_connect('127.0.0.1:3306', 'user', 'pass') or die('Could not connect: ' . mysql_error()); mysql_select_db('database'); $insert = "INSERT INTO table (field1, field2, field3) Values $data"; mysql_query($insert) or die(mysql_error()); ?> Try that Widow PS thanks toon for the more efficient query string Quote Link to comment Share on other sites More sharing options...
SpeedBird Posted February 26, 2007 Author Share Posted February 26, 2007 The database I'm querying is MS Access, the database I'm inserting to is MySQL. I don't actually need to worry about datatype conversions (text is fine). Quote Link to comment Share on other sites More sharing options...
SpeedBird Posted February 26, 2007 Author Share Posted February 26, 2007 A very big thank you that did it! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.