Connecting to 2 databases at the same time to copy a field to a second database

Question

I have a field in my table that I need to move to a completely different database. At this point I have 1 database db1 that has db1table that has all the data, and an empty database db2 that has db2table.

db1 table looks like this:

id    other_db_id     data_to_be_moved
---------------------------------------
1     NULL            data
2     NULL            data 
3     NULL            data 
4     NULL            data
5     NULL            data

db2 table looks like this:

id      data
--------------
empty

I usually use an ORM to access the database, but this time I'm doing it with plain mysql and php, so need a little help, especially with how I'd connect to 2 databases at the same time.

What I'd like to do is select the first 10 records from db1 table, read the field data_to_be_moved and use it to create a new record in db2 table. Then get the id of the newly inserted record and insert back in the original database as field other_db_id.

The way I'm connecting to a single database is this. How will I access both databases at the same time?

$connection = mysql_connect("localhost", "db1user","db1pass");
mysql_select_db("db1", $connection);

and I'm selecting the first 10 records to be manipulated as follows:

Select * From table Where Id BETWEEN 5 AND 10;

but I'm not sure how to proceed with the switching of the databases to achieve what I described above.

Try to look http://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage — Marino Di Clemente, Sep 22 '12 at 18:10

score 1 · Answer 1 · answered Sep 22 '12 at 18:10

1

Basically you need to know how to handle multiple databases. The following video will explain how to deal whit two (or more) databases: video

answered Sep 22 '12 at 18:10

faq

2,965
5
27
35

score 1 · Answer 2 · answered Sep 22 '12 at 18:12

1

You could store the intermediate values into a PHP variable, then switch database and do your thing.

answered Sep 22 '12 at 18:12

Ruud

11
1

score 0 · Answer 3 · answered Sep 22 '12 at 18:41

If the databases are on same server you can access both tables at same time by using syntax:

insert into db2.db2table
select other_db_id, data_to_be_moved
from db1.db1table;

This requires that the login has at least select access to other database.

If the databases are on different server, you can use federated table:

http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

score 0 · Answer 4 · answered Sep 22 '12 at 18:47

0

Um,

$connection2 = mysql_connect("localhost", "db2user","db2pass");

answered Sep 22 '12 at 18:47

Alain Collins

16,268
2
32
55

Connecting to 2 databases at the same time to copy a field to a second database

4 Answers4