simultaneous connections to different mysql databases

[p4rsec]

I'm trying to migrate data from an old, poorly designed database to a new one using a php script.
I don't know if I'm just not using mysql_select_db correctly or what.

the old database is called 'od'
the new one is 'resource_centre'

my code is:

$od_con = mysql_connect('localhost','od','dialogue');
if ( mysql_select_db('od',$od_con) ) {
        print "od_con selected od<br>";
}

$rc_con = mysql_connect('localhost','od','dialogue');

if ( mysql_select_db('resource_centre',$rc_con) ) {
        print "rc_con selected resource_centre<br>";
}


//Go through each link from the original (od) database:
$query = "SELECT id, name_english, name_french, url_english, url_french, 
                 description_english, description_french, 
                 both_lang_english, both_lang_french
          FROM link";
if ( ! $link_result = mysql_query($query, $od_con) ) {
        print mysql_error() . "<br>";
}

both mysql_select_db calls return true
however, I get an error with the query, which I want to being querying the 'od' database (hence the use of $od_con) but it's apparently querying the resource_centre database (since if I comment out the second mysql_select_db, the query works).

I need to do inserts into the resource_centre database for each row in the od table.

Any thoughts?

[[deleted]]

PHP uses connection-pooling, it will never open more than one real database connection to the same database/user/password combination.

So, when you open the 'second' connection, you actually get a pointer to the first connection. And your second 'select_db' just changes the selected DB for that connection, undo-ing whatever the first select_db did.

Bottom line, you cannot open two connections to the same database at the same time.

[buck2bcr]

Instead of selecting a database couldn't you just make a mysql link then inside each query tell it which database to pull from?

I use this when I have to get info from two seperate databases on the same page. I have two differnt databases (tracking and payroll) and the queries look like this.

$query_daily = "SELECT tracking.daily.* FROM tracking.daily WHERE tracking.daily.day=\"$id\"";
    $result_daily = mysql_query($query_daily,$mysql_link);

$query_driver = "SELECT payroll.names.* FROM payroll.names WHERE payroll.names.badge=\"$driver_badge\"";
            $result_driver = mysql_query($query_driver,$mysql_link);

This uses tracking as the database and daily as the table, then payroll as database and names as the table seems to work alright. You could use it in yours using:

database.table.field

HTH