[SOLVED] Query rows from one database and insert them into another

[SpeedBird]

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?

[SpeedBird]

*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.

[craygo]

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

[SpeedBird]

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).

[SpeedBird]

A very big thank you that did it!