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.

1
178
7/7/2014 5:14:50 PM

Accepted Answer

Warning : mysql_xx functions are deprecated since php 5.5 and removed since php 7.0 (see http://php.net/manual/intro.mysql.php), use mysqli_xx functions or see the answer below from @Troelskn


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. For example:

$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 pass the first link identifier:

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

and for database 2 pass the second:

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

If you do not pass a link identifier then the last connection created is used (in this case the one represented by $dbh2) e.g.:

mysql_query('select * from tablename');

Other options

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 connection) you could:

  • Keep one connection open and call mysql_select_db() to swap between as necessary. I am not sure this is a clean solution and you could end up querying the wrong database.
  • Specify the database name when you reference tables within your queries (e.g. SELECT * FROM database2.tablename). This is likely to be a pain to implement.

Also please read troelskn's answer because that is a better approach if you are able to use PDO rather than the older extensions.

330
2/18/2019 2:11:46 PM

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

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon