How to Connect to Multiple MySQL Databases on a Single Web Page In PHP?

Problem:

You want to use more than one database on a single PHP script. You’re looking for a way to do this.

Solution:

In this tutorial, using three PHP APIs (API – Application Programming Interface), we’ll connect two different MySQL databases on a single PHP page. The three APIs are-

1. PHP’s MySQL Extension
2. PHP’s mysqli Extension
3. PHP Data Objects(PDO)

Here are the settings of our two sample databases –

Host: localhost
Username: root
Password:
Database 1: db_students
Database 2: db_employees

In the following, we’ll use each API to connect with the above two databases.

1. Using PHP’s MySQL Extension

The following code opens two MySQL server connections ($link1 and $link2) and then each connection will select one database to use-

[php]
<?php
$host = “localhost”;
$user  = “root”;
$password =  “”;
$database1 = ”db_students”;
$database2 = ”db_employees”;
$link1 = mysql_connect($host, $user, $password);
if(!$link1) {
die(“Not connected: ”. mysql_error());
}else{
mysql_select_db($database1, $link1);
}

$link2 = mysql_connect($host, $user, $password, TRUE);
if(!$link2) {
die(“Not connected: ”. mysql_error());
}else{
mysql_select_db($database2, $link2);
}
?>
[/php]

Explanation:

2. Using PHP’s mysqli Extension

The following code instantiates two instances of mysqli ($dbh1 and $dbh2), each instance uses one database –

[php]

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database1 = "db_students";
$database2 = "db_employees";
$dbh1 = new mysqli($host, $user, $password, $database1);
if($dbh1->connect_errno > 0){
die(‘Unable to connect to database’ . $dbh1->connect_error);
}else{
echo "Database db_student is connected.";
}

$dbh2 = new mysqli($host, $user, $password, $database2);
if($dbh2->connect_errno > 0){
die(‘Unable to connect to database’ . $dbh2->connect_error);
}else{
echo "Database db_employee is connected.";
}
?>
[/php]

Explanation:

3. Using PHP Data Objects (PDO)

The following code instantiates two instances of PDO ($dbh1 and $dbh2). First instance uses the database “db_students” and the second one uses “db_employees”-

[php]
<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database1 = "db_students";
$database2 = "db_employees";

try {
$dbh1 = new PDO("mysql:host=$host;dbname=$database1", $user, $password);
} catch(PDOException $e) {
die(‘Could not connect to the database:’ . $e);
}

try {
$dbh2 = new PDO("mysql:host=$host;dbname=$database2", $user, $password);
} catch(PDOException $e) {
die(‘Could not connect to the database:’ . $e);
}
?>
[/php]

Explanation: