Connect to two databases?

karimali831 · #1 (**permalink**) 01-12-09, 12:10 AM

Is this possible? I want to connect to two databases from one php page. Both databases are on the same machine also. I use a CMS and want it to read a group of tables from db1, and another group of tables frmo db2.

any help much appreciated

thank you

**sysadmin** · #2 (**permalink**) 01-12-09, 05:11 PM

Quote:

Originally Posted by karimali831

Is this possible? I want to connect to two databases from one php page. Both databases are on the same machine also. I use a CMS and want it to read a group of tables from db1, and another group of tables frmo db2.

any help much appreciated

thank you

Hi Karim,

If you are trying to open multiple, separate MySQL connections with the same MySQL user, password, & hostname/IP, you must set $new_link = TRUE to prevent mysql_connect from using an existing connection. This parameter has been available since PHP 4.2.0 and allows you to open a new link even if the call uses the same parameters.

For example, you are opening two separate connections to two different databases (but on the same host, & with the same user & password):

PHP Code:


			
$db1 = mysql_connect($dbhost, $dbuser, $dbpass);
$rv = mysql_select_db($dbname1, $db1);
$db2 = mysql_connect($dbhost, $dbuser, $dbpass);
$rv = mysql_select_db($dbname2, $db2);

At this point, both $db1 & $db2 will have selected the database named by $dbname2.

The workaround is to require that the second MySQL connection is new:

PHP Code:


			
$db1 = mysql_connect($dbhost, $dbuser, $dbpass);
$rv = mysql_select_db($dbname1, $db1);
$db2 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE);
$rv = mysql_select_db($dbname2, $db2);

Now, $db1 should have selected $dbname1, & $db2 should have selected $dbname2.

ie: by calling mysql_select_db($database) before every SQL query just to be sure which database you are working with.

Note: This occurs only when the server, username, & password parameters are identical for each mysql_connect statement..

karimali831 · #3 (**permalink**) 01-12-09, 09:02 PM

I'm always confused with this SQL stuff but I hope you understand this. From the CMS, the database info is like:

Code:

<?php
 $host = "localhost";
 $user = "";
 $pwd = "";
 $db = "";

mysql_connect($host, $user, $pwd) or system_error('ERROR: Can not connect to MySQL-Server');
mysql_select_db($db) or system_error('ERROR: Can not connect to database "'.$db.'"');

With the code you provided in your post can you help me add it to make the complete above code if I'm making sense or do you require more info?

Help much appreciated, thank you!

**sysadmin** · #4 (**permalink**) 02-12-09, 02:38 PM

Quote:

Originally Posted by karimali831

I'm always confused with this SQL stuff but I hope you understand this. From the CMS, the database info is like:

Code:

<?php
 $host = "localhost";
 $user = "";
 $pwd = "";
 $db = "";

mysql_connect($host, $user, $pwd) or system_error('ERROR: Can not connect to MySQL-Server');
mysql_select_db($db) or system_error('ERROR: Can not connect to database "'.$db.'"');

With the code you provided in your post can you help me add it to make the complete above code if I'm making sense or do you require more info?

Help much appreciated, thank you!

Try this:

PHP Code:


			
// common host because both db on the same  server
$host = "localhost";

// User & pass for first database
$user1 =  "";
$pwd1 = "";
$db1 = "";

// User & pass for second database
$user2 =  "";
$pwd2 = "";
$db2 = "";

// Connect to first Database and get link for  connection
$link1 = mysql_connect($host, $user1, $pwd1) or  system_error('ERROR: Can not connect to MySQL-Server');

//Select First  
mysql_select_db($db1,$link1) or system_error('ERROR: Can not connect to  database "'.$db1.'"');

// Connect to second Database and get link for  connection
$link2 = mysql_connect($host, $user2, $pwd2) or  system_error('ERROR: Can not connect to MySQL-Server');
//Select First  
mysql_select_db($db2,$link2) or system_error('ERROR: Can not connect to  database "'.$db2.'"');

//Select query for first database
$result =  mysql_query("select * from db1",$link1);

//Select query for second database
$result =  mysql_query("select * from db2",$link2);