How do you connect to multiple MySQL databases on

Question

I have information spread out across a few databases and want to put all the information onto one webpage using PHP. I was wondering how I can connect to multiple databases on a single PHP webpage.

I know how to connect to a single database using:

$dbh = mysql_connect($hostname, $username, $password) 
        or die("Unable to connect to MySQL");

However, can I just use multiple "mysql_connect" commands to open the other databases, and how would PHP know what database I want the information pulled from if I do have multiple databases connected.

Answer 1

You don't actually need select_db. You can send a query to two databases at the same time. First, give a grant to DB1 to select from DB2 by GRANT select ON DB2.* TO DB1@localhost;. Then, FLUSH PRIVILEGES;. Finally, you are able to do 'multiple-database query' like SELECT DB1.TABLE1.id, DB2.TABLE1.username FROM DB1,DB2 etc. (Don't forget that you need 'root' access to use grant command)

Answer 2

if you are using mysqli and have two db_connection file. like first one is

define('HOST','localhost');
define('USER','user');
define('PASS','passs');
define('**DB1**','database_name1');

$connMitra = new mysqli(HOST, USER, PASS, **DB1**);

second one is

    define('HOST','localhost');
    define('USER','user');
    define('PASS','passs');
    define(**'DB2**','database_name1');

    $connMitra = new mysqli(HOST, USER, PASS, **DB2**);

SO just change the name of parameter pass in mysqli like DB1 and DB2. if you pass same parameter in mysqli suppose DB1 in both file then second database will no connect any more. So remember when you use two or more connection pass different parameter name in mysqli function

Answer 3

You might be able to use MySQLi syntax, which would allow you to handle it better.

Define the database connections, then whenever you want to query one of the database, specify the right connection.

E.g.:

$Db1 = new mysqli('$DB_HOST','USERNAME','PASSWORD'); // 1st database connection 
$Db2 = new mysqli('$DB_HOST','USERNAME','PASSWORD'); // 2nd database connection

Then to query them on the same page, use something like:

$query = $Db1->query("select * from tablename")
$query2 = $Db2->query("select * from tablename")
die("$Db1->error");

Changing to MySQLi in this way will help you.

Answer 4

Instead of mysql_connect use mysqli_connect.

mysqli is provide a functionality for connect multiple database at a time.

$Db1 = new mysqli($hostname,$username,$password,$db_name1); 
// this is connection 1 for DB 1

$Db2 = new mysqli($hostname,$username,$password,$db_name2); 
// this is connection 2 for DB 2

Answer 5

If you use PHP5 (And you should, given that PHP4 has been deprecated), you should use PDO, since this is slowly becoming the new standard. One (very) important benefit of PDO, is that it supports bound parameters, which makes for much more secure code.

You would connect through PDO, like this:

try {
  $db = new PDO('mysql:dbname=databasename;host=127.0.0.1', 'username', 'password');
} catch (PDOException $ex) {
  echo 'Connection failed: ' . $ex->getMessage();
}

(Of course replace databasename, username and password above)

You can then query the database like this:

$result = $db->query("select * from tablename");
foreach ($result as $row) {
  echo $row['foo'] . "\n";
}

Or, if you have variables:

$stmt = $db->prepare("select * from tablename where id = :id");
$stmt->execute(array(':id' => 42));
$row = $stmt->fetch();

If you need multiple connections open at once, you can simply create multiple instances of PDO:

try {
  $db1 = new PDO('mysql:dbname=databas1;host=127.0.0.1', 'username', 'password');
  $db2 = new PDO('mysql:dbname=databas2;host=127.0.0.1', 'username', 'password');
} catch (PDOException $ex) {
  echo 'Connection failed: ' . $ex->getMessage();
}

Answer 6

$dbh1 = mysql_connect($hostname, $username, $password);  
$dbh2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('database1', $dbh1); 
mysql_select_db('database2',$dbh2); 

mysql_query('select * from tablename', $dbh1);
mysql_query('select * from tablename', $dbh2);

This is the most obvious solution that I use but just remember, if the username / password for both the database is exactly same in the same host, this solution will always be using the first connection. So don't be confused that this is not working in such case. What you need to do is, create 2 different users for the 2 databases and it will work.