How to connect to multiple databases in a single P

Question

Possible Duplicate:
How do you connect to multiple MySQL databases on a single webpage?

If I want to connect to one db do some query, and then later do another query from another DB. How do I do it? Do I just

 mysql_pconnect("host:3306", "user", "password") or die(mysql_error());
 mysql_select_db("Test") or die(mysql_error());

//do some query

 mysql_pconnect("host2:3306", "user", "password") or die(mysql_error());
 mysql_select_db("Test") or die(mysql_error());

//do another query

Is that how you do it? A couple of questions. Notice I used pconnect, does that affect calling it twice on the same page? Also, do I have to close the connection for the first one before calling the second one?

Answer 1

You need to store database connection link in separate variable. For example

 $connection_1 = mysql_connect("host:3306", "user", "password") or die(mysql_error());
 mysql_select_db("Test", $connection_1) or die(mysql_error());

 $connection_2 = mysql_pconnect("host2:3306", "user", "password") or die(mysql_error());
 mysql_select_db("Test", $connection_2) or die(mysql_error());

 mysql_query("your query", $connection_1); // run query for first connection
 mysql_query("your query", $connection_2); // run query for second connection

Answer 2

Is that how you do it?

This will leave your script with two open connections to different hosts until it ends.

You may reuse either of these connections by calling mysql_pconnect again.

Notice I used pconnect, does that affect calling it twice on the same page?

From the docs:

The function would first try to find a (persistent) link that's already open with the same host, username and password

Since your hosts are different, there will be two different connections

Also, do I have to close the connection for the first one before calling the second one?

You cannot explicitly close a connection open with mysql_pconnect.

Answer 3

You did RTM, right, because you're not using the $link_identifier?

http://us.php.net/mysql_select_db :

bool mysql_select_db ( string $database_name [, resource $link_identifier ] )

Sets the current active database on the server that's associated with the specified link identifier. Every subsequent call to mysql_query() will be made on the active database.

Parameters

database_name The name of the database that is to be selected.

link_identifier The MySQL connection.

If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Whenever you have use the pconnect, you're sharing the connection (not only within the page, but possibly with other pages) -- in your case here, don't do that. You want isolated links, therefore isolated transactions. You should probably consider mysql_connect instead, and explicitly using the new_link parameter. Lastly, use the $link_identifier explicitly, so you are clear what you are connecting to.

Answer 4

You need to store the resource returned from mysql_connect and use it when doing mysql_select_db.

$res1 = mysql_pconnect(...);
mysql_select_db("Test", $res1);

$res2 = mysql_pconnect(...);
mysql_select_db("Test", $res2);

Then use $res1 or $res2 when querying the corresponding db.

mysql_query("select * from test_table", $res1);

mysql_query("select * from test_table", $res2);