Connecting to multiple databases with PDO.

This is a short tutorial on how to connect to multiple MySQL databases in the same script using the PDO object.

With the older mysql_select_db function, you could easily connect to MySQL and then switch between databases like so:

//Connect to MySQL.
$conn = mysql_connect('localhost', 'root', '');

//Select database one.
mysql_select_db('database_one', $conn);

//Query database one.

//Select database two.
mysql_select_db('database_two', $conn);

//Query database two.

As you can see, the same MySQL connection can be used throughout the script. If you want to change the database, then all you need to do is call the mysql_select_db function.

Simple, right?

However, with the PDO object, things work a bit differently. This is because the object doesn’t have a similar function. Instead, you must create a PDO object for each database that you intend on querying.

Take a look at the following example:

//Connect to MySQL and select database one.
$databaseOne = new PDO('mysql:host=localhost;dbname=database_one', 'root', '');

//Query database one using $databaseOne.

//Connect to MySQL and select database two.
$databaseTwo = new PDO('mysql:host=localhost;dbname=database_two', 'root', '');

//Query database two using $databaseTwo.

With the PDO object, both the connection and the selected database are contained in the object. This is because the name of the database is selected via the DSN string, which is passed in via the constructor.

Although this might seem like a bit of a pain at first, it does help to keep your code far more organized. If you are using multiple connections, then you should try to give your objects an appropriate and identifiable name. That way, you won’t confuse one for the other.