JoshFinnie JoshFinnie - 9 months ago 37
PHP Question

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

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.


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.