How do you connect to multiple MySQL databases on a single webpage?

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 can make multiple calls to mysql_connect(), but if the parameters are the same you need to pass true for the '$new_link' (fourth) parameter, otherwise the same connection is reused.

so then you have

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

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

Then to query database 1, do

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

and for database 2

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

Alternatively, if the mysql user has access to both databases and they are on the same host (i.e. both DBs are accessible from the same MySQL connection) you could:

• Keep one connection open and keep calling mysql_select_db() to swap between. I don't think this is a clean solution and you will easily get cases where you query the wrong db etc.
• Use queries where you specify the database name (e.g. SELECT * FROM database2.tablename), but this is again likely to be a pain to implement.

Answer 2

You might also want to take a look at one of the many database abstraction tools around. However they will generally do the same thing that tom described to keep multiple connections open at the same time.

Answer 3

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();
}