Connect to two different MySQL databases at the same time
Moderator: General Moderators
Connect to two different MySQL databases at the same time
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!
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
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.
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.
-
php_wiz_kid
- Forum Contributor
- Posts: 181
- Joined: Tue Jun 24, 2003 7:33 pm
Re: Connect to two different MySQL databases at the same time
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?
- 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
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
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.
Then in my web pages I include the connection code and run my queries.
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)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.- 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
First of all, don't use the error suppression symbol (@) before your function calls.
Here is an example of what I described:
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
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.
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.
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.Re: Connect to two different MySQL databases at the same time
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
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
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?
- 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
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 wrote:mysql_connect("http://www.bungkusan.com","username","password");
Re: Connect to two different MySQL databases at the same time
wuahh..
lol, stupid idea right?
I have tried this stuff and have zero result
anybody try this either?
lol, stupid idea right?
I have tried this stuff and have zero result
anybody try this either?
- 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
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.
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.