Using PHP to transfer data between 2 MySQL databases 
Author Message
 Using PHP to transfer data between 2 MySQL databases

Hello,

I am proficient in PHP to be able to code a web application that takes data from one
table and inserts/updates it into another table.

But how do I do this if each table exists in 2 different databases on 2 different
servers?

If I select a database, the other database is no longer selected.

For example:

$link = mysql_connect( $host, $db_user, $db_pass );
mysql_select_db($db);

I'm connecting no problem to each, but how to get 2 connections simultaneously to
transfer data between the tables?

Cheers,
Lee.



Sun, 31 Jul 2005 04:05:42 GMT  
 Using PHP to transfer data between 2 MySQL databases

Quote:

> If I select a database, the other database is no longer selected.

> For example:

> $link = mysql_connect( $host, $db_user, $db_pass );
> mysql_select_db($db);

I haven't tried this but

mysql_select_db($db, $link);

should work.

--
Andy.



Sun, 31 Jul 2005 04:20:21 GMT  
 Using PHP to transfer data between 2 MySQL databases

Quote:

>>If I select a database, the other database is no longer selected.

>>For example:

>>$link = mysql_connect( $host, $db_user, $db_pass );
>>mysql_select_db($db);

I think you'll need to make multiple connections to the mysql server.
You could also store all the SELECTed data into variables, then INSERT
it into the new database.

--
Ryan



Sun, 31 Jul 2005 05:01:34 GMT  
 Using PHP to transfer data between 2 MySQL databases

Quote:

> Hello,

> I am proficient in PHP to be able to code a web application that
> takes data from one table and inserts/updates it into another table.

> But how do I do this if each table exists in 2 different databases on
> 2 different servers?

> If I select a database, the other database is no longer selected.

> For example:

> $link = mysql_connect( $host, $db_user, $db_pass );
> mysql_select_db($db);

> I'm connecting no problem to each, but how to get 2 connections
> simultaneously to transfer data between the tables?

> Cheers,
> Lee.

Don't do it that way.

$sql = "INSERT INTO db1.table1 SELECT field1,field2,...,fieldn FROM
db2.table2 ..."

--
Nick Grimshaw



Mon, 01 Aug 2005 22:50:05 GMT  
 Using PHP to transfer data between 2 MySQL databases


Quote:

> > Hello,

> > I am proficient in PHP to be able to code a web application that
> > takes data from one table and inserts/updates it into another table.

> > But how do I do this if each table exists in 2 different databases on
> > 2 different servers?

> > If I select a database, the other database is no longer selected.

> > For example:

> > $link = mysql_connect( $host, $db_user, $db_pass );
> > mysql_select_db($db);

> > I'm connecting no problem to each, but how to get 2 connections
> > simultaneously to transfer data between the tables?

> > Cheers,
> > Lee.

> Don't do it that way.

> $sql = "INSERT INTO db1.table1 SELECT field1,field2,...,fieldn FROM
> db2.table2 ..."

> --
> Nick Grimshaw

the above example will only work if the databases are on the same  mysql
server. If on separate ones then the way I would do it is to open 2 links
i.e.

$link_db1 = mysql_connect( $host, $db_user, $db_pass );
mysql_select_db($db1);
$link_db2 = mysql_connect( $host, $db_user, $db_pass );
mysql_select_db($db2);
$sql1 = "SELECT * FROM $source_table";
$resid1 = mysql_query($sql1,$link_db1);
while ($row=mysql_fetch_assoc($resid1)) {
    $sql2 = "INSERT INTO `$destination_table` (`".implode("`,
`",array_keys($row))."`) VALUES ('".implode("', '",$row)."')";
    $resid2 = mysql_query($sql2,$link_db2);

Quote:
}

Note the difference between using single quotes in the values list and `
chars around the table name and field list. also use of mysql_escape_string
my be necessary dependant on what data you're transferring. To do this using
the same implode method shown above however you'd need to use the array_walk
function first

hope this helps



Fri, 05 Aug 2005 17:30:49 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. PHP / MySQL / VFP Database

2. empty cells in mysql database and PHP

3. PHP, mySQL and connection to a SQL Database

4. transferring a lot of data from Access Database to Labview

5. Inserting data from php form to MySQL

6. drawing a binary tree with php gd from mysql data

7. Floats and LOAD DATA (PHP & MySQL)

8. formatting MySQL/PHP data into columns, rows, ecc.

9. PHP not inserting data correctly into MySQL

10. Using remote MySQL server with PHP via HTTP

11. How do I write flattened to string data to a MySQL database

12. Sending data to MySQL Database

 

 
Powered by phpBB® Forum Software