multiple database connections

need some help

[willis1480]

Im using PHP and Mysql. I need to transfer data from "database_1.table_1" to "database_2.table_2".

Table_1 has 10,000 rows, so any line by line searches and inserts do not work. Can I connect to 2 different databases at the same time? pconnect is not really doing what I need.

An alternative I was thinking would be to create an SQL export from DB1 and save as text file on the server and then upload the .txt file to DB2.

Essentially creating a temp table in DB2. Then I could run my queries and inserts no problem.

Anyone done anything like this in PHP? Can't figure out how to export the SQL syntax like PHPmyadmin does.

Thanks

[jatar_k]

for getting the export you can find the syntax here
[dev.mysql.com...]

you can have two seperate connections to the same server and have each one select a different database. The problem with this is you are using up double the connections. You may run into issues with max number of connections if many people connect to this.

When I read your post though I did think you may have a larger issue, this just seems like a bad plan.

why not have them in the same database if they need to be cross referenced all the time?

or use a third that keeps all the data from both if they truly need to be seperate and you need lookups once in a while.

[willis1480]

Ideally the tables would all be held in 1 database, but that is not really an option here.

I ended up just moving all the data from the 1 database to the other and running my queries on one DB using php. It is a little painful, but we need to run this once everyday to maintain correct pricing so wanted to have a one click solution.

It currently runs ok as is, but I do have some concerns for if the database gets substantially larger. Then I may be forced to combine the two databases.

Thanks for the input.