Connect to two different MySQL databases at the same time

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

bckennyw
Forum Newbie
Posts: 11
Joined: Wed Nov 26, 2003 10:25 am

Connect to two different MySQL databases at the same time

Post by bckennyw »

Is it possible to connect to two MySQL database at once using PHP?

If so, how should I handle the connection? Since mysql_query($theQuery) would ask one of the databases for "something," if I connect to two or more databases at the same time, does it know which one to ask for?

I am not talking about something like... connect to A, ask A something, close A, and then connect to B, ask B something, close B, and connect back to A, ask A something... (Keep going).

It is because the two database have some data in common, and the on-going connecting & closing would really annoy me... (I constantly need to ask database from both databases at the same time.) I wonder if there's another way to do it. If not, that's okay. If so, could you show me please?

Thanks a lot in advance!
php_wiz_kid
Forum Contributor
Posts: 181
Joined: Tue Jun 24, 2003 7:33 pm

Post by php_wiz_kid »

In a query you can ask MySQL to pull something from a database that you havn't opened a connection to using the mysql_connect() or mysql_pconnect() functions. To do this you tell the query to look in dbB.tablename (dbB would be the database, and the .tablename would be the name of the table you're wanting to get the data from).

Example.
SELECT user_id FROM dbB.tablename WHERE id = 1

This query, while the connection is set to dbA, would look in dbB in the table name provided.
bckennyw
Forum Newbie
Posts: 11
Joined: Wed Nov 26, 2003 10:25 am

Post by bckennyw »

Thanks a lot for your help! Problem solved!
php_wiz_kid
Forum Contributor
Posts: 181
Joined: Tue Jun 24, 2003 7:33 pm

Post by php_wiz_kid »

Glad I could be of help. Don't be afraid to e-mail me if you have any other PHP/MySQL related problems.
rbc
Forum Newbie
Posts: 22
Joined: Tue Sep 02, 2008 6:00 pm

Re: Connect to two different MySQL databases at the same time

Post by rbc »

Is this the only way to connect to two DBs at the same time or is it possible ot open two connections and leave them open to perform multiple queries?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Connect to two different MySQL databases at the same time

Post by jayshields »

Yeah, when you use mysql_connect(), save the returned connections into seperate variables. Then when you use mysql_*() just make sure you pass the appropriate connection variable as a parameter too.
rbc
Forum Newbie
Posts: 22
Joined: Tue Sep 02, 2008 6:00 pm

Re: Connect to two different MySQL databases at the same time

Post by rbc »

Can you direct me to an example of this in code. I'm fairly new to MySQL and having troubles visualizing this. Here's and example of how I'm currently connecting to one DB.

Code: Select all

// Make the connection.
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );
 
// Select the database.
@mysql_select_db (DB_NAME)
Then in my web pages I include the connection code and run my queries.

Code: Select all

// Connect to the database.
require_once ('connection script path here'); 
 
$query = "SELECT * FROM table WHERE x=y";
$result = @mysql_query ($query); // Run the query.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Connect to two different MySQL databases at the same time

Post by jayshields »

First of all, don't use the error suppression symbol (@) before your function calls.

Here is an example of what I described:

Code: Select all

$link = mysql_connect('hostname one', 'user', 'pass') or die(mysql_error());
$other_link = mysql_connect('hostname two', 'user', 'pass') or die(mysql_error());
 
mysql_select_db('database_abc', $link);
mysql_select_db('database_xyz', $other_link);
 
$result = mysql_query('SELECT * FROM `table_abc`', $link);
$other_result = mysql_query('SELECT * FROM `table_xyz`', $other_link);
rbc
Forum Newbie
Posts: 22
Joined: Tue Sep 02, 2008 6:00 pm

Re: Connect to two different MySQL databases at the same time

Post by rbc »

This worked great! Thanks for your help. The only issue I have with it is that it has to know the $link or $other_link in the query. Is it possible for that to be determined by another variable and then passed to the query?

I have a situation I'd like to run a query and then based on the query result use that to set $link.

Code: Select all

$q = "SELECT * FROM tablename";
$r = mysql_query ($q, $dbc1);
            
$row = mysql_fetch_array($r, MYSQL_ASSOC);
$dbconnect = $row['db_connection'];
            .
$q_two = "SELECT * FROM tablename";
$r_two = mysql_query ($q_two, $dbconnect); // Run the query.
When I try to do this I get the correct info in the variable $dbconnect, but when I use that variable to pass in the query I get a MySQL result resource error.
rbc
Forum Newbie
Posts: 22
Joined: Tue Sep 02, 2008 6:00 pm

Re: Connect to two different MySQL databases at the same time

Post by rbc »

Got it to work. I had to add $ to $dbconnect so it would pass it as a variable. $$dbconnect works. Thanks for all your help!!!
bungkusan
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2008 8:56 am

Re: Connect to two different MySQL databases at the same time

Post by bungkusan »

:? thinking about something :

mysql_connect("localhost","username","password");

I've tried to connect from another website, as example :

I have 3 website in common :
http://www.iklan-laris.com
http://www.bungkusan.com
http://www.mobilpilihan.com

I wanna fetch data from bungkusan.com, when I was in mobilpilihan.com may I use this code?

mysql_connect("http://www.bungkusan.com","username","password");

changing the localhost with my link to it's database ?

any idea for solving this problem?
rbc
Forum Newbie
Posts: 22
Joined: Tue Sep 02, 2008 6:00 pm

Re: Connect to two different MySQL databases at the same time

Post by rbc »

That I'm not sure of. My situation was done with all databases located on the same server. Not a cross server connect. Though I think it might be possible. Have you tried it? Do you get an error?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Connect to two different MySQL databases at the same time

Post by jayshields »

bungkusan wrote:mysql_connect("http://www.bungkusan.com","username","password");
That might work as it is. If not, try appending a port number preceeded by a colon (:). The default port is 3306. If that doesn't work try using the servers IP instead of the domain name, with and without the port number.
bungkusan
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2008 8:56 am

Re: Connect to two different MySQL databases at the same time

Post by bungkusan »

wuahh..
lol, stupid idea right?

I have tried this stuff and have zero result :banghead:

anybody try this either?
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Connect to two different MySQL databases at the same time

Post by jayshields »

On your http://www.bungkusan.com website, when connecting to MySQL, do you use localhost as the hostname? If so, and you haven't modified the default port to use, then I believe that simply using http://www.bungkusan.com should work, or some variation of it as described in my previous post.

I have used remote servers for MySQL databases before, but specified the IP, according to the manual it can also accept domain names, and just get the IP from it I guess.
Post Reply