extracting data from mysql db to another

Question

I have two mysql databases with the same structure in each.

I want to take selected data from one db to the other.

One of the tables holds mutiple rows of data and I was looking at looping through the rows and doing inserts into the second db table but realised that as soon as I connect to the second db I will lose the connection to the first and the loop will fail.

Is the a robust and 'proper' way of doing theis rather than creating an array and then running an insert loop in the second db from the array?

currently I am using two different lots of credetials to access each db.

the API fuctons I am using are mysql_query and mysql_insert.

the reason i need to do this is I have a backend system that allows me to upload templates to the server, some are live and some are not.

the second db holds user details and details of any templates that they have selected.

btw I am working with php.

many thanks in advance and I hope I am not asking you to spoon feed me

cheers Barry

1. Do you use different credentials to access these databases? Which API (function names) you are using? 3. Why do you need such a transfer at all? Please answer not in comment but by editing your question — Your Common Sense
– Your Common Sense, Commented Feb 18, 2013 at 10:13
You'd probably be much better off just dumping the database from the source and restoring it to the destination. — GordonM
– GordonM, Commented Feb 18, 2013 at 10:29
GordonM, I need to do this on the fly. The user will select a template from db1 and the details of that template needs to be pushed in the db holding the user details, which is db2 — Barry Watts
– Barry Watts, Commented Feb 18, 2013 at 10:29

Ejzy · Accepted Answer · 2013-02-18 11:07:28Z

1

You are able to use 2 connections simultaneously by using $link_identifier parameter in mysql_* functions:

$connection1 = mysql_connect('host1', 'user1', 'password1');
$connection2 = mysql_connect('host2', 'user2', 'password2');

... 

$resource = mysql_query('SELECT .....', $connection1);

...

mysql_query('INSERT .....', $connection2);

...

mysql_close($connection1);
mysql_close($connection2);

answered Feb 18, 2013 at 11:07

Ejzy

4105 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Your Common Sense · Accepted Answer · 2013-02-18 10:38:07Z

0

mysql_connect has an extra parameter new_link.
mysql_connect returns a connection resource.
mysql_* functions can use that resource variable to make their calls on the certain connection.

So, just create 2 connections returning their resources and then use these resource variables to tell mysql_query() which database to use

answered Feb 18, 2013 at 10:38

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Collectives™ on Stack Overflow

extracting data from mysql db to another

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related