Page 1 of 2
Connect to two different MySQL databases at the same time
Posted: Tue Jan 06, 2004 10:13 am
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!
Posted: Tue Jan 06, 2004 11:18 am
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.
Posted: Tue Jan 06, 2004 12:36 pm
by bckennyw
Thanks a lot for your help! Problem solved!
Posted: Tue Jan 06, 2004 5:01 pm
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.
Re: Connect to two different MySQL databases at the same time
Posted: Fri Sep 12, 2008 9:16 am
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?
Re: Connect to two different MySQL databases at the same time
Posted: Fri Sep 12, 2008 9:45 am
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.
Re: Connect to two different MySQL databases at the same time
Posted: Fri Sep 12, 2008 10:13 am
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.
Re: Connect to two different MySQL databases at the same time
Posted: Fri Sep 12, 2008 12:42 pm
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);
Re: Connect to two different MySQL databases at the same time
Posted: Sat Sep 13, 2008 9:39 am
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.
Re: Connect to two different MySQL databases at the same time
Posted: Sat Sep 13, 2008 10:15 am
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!!!
Re: Connect to two different MySQL databases at the same time
Posted: Tue Sep 16, 2008 10:22 am
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?
Re: Connect to two different MySQL databases at the same time
Posted: Thu Sep 18, 2008 10:09 am
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?
Re: Connect to two different MySQL databases at the same time
Posted: Thu Sep 18, 2008 10:16 am
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.
Re: Connect to two different MySQL databases at the same time
Posted: Thu Sep 18, 2008 6:44 pm
by bungkusan
wuahh..
lol, stupid idea right?
I have tried this stuff and have zero result
anybody try this either?
Re: Connect to two different MySQL databases at the same time
Posted: Fri Sep 19, 2008 5:36 am
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.