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)

An API is a set of instructions and standards used to communicate with a software.
APIs can be procedural or object-oriented. In the above APIs, the first one “MySQL Extension” is a procedural API and the next two are object oriented APIs. To use a procedural API, just call its function. On the other hand, to use an object-oriented API, first instantiate the class and then call the method on the object. You need to have knowledge on object oriented programming to understand the usage of object oriented APIs.

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
$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);
}
?>

Explanation:

Line 7 mysql_connect function establishes a connection and returns that connection link identifier($link1).
Line 11 Using the connection link ($link1) that we created on line 7, we select the database db_student.
Line 14 Here, another connection is established and the connection link($link2) is returned. As we’re trying to establish a new connection with the same mysql_connect() parameters, we used TRUE in the fourth parameters. If we don’t use the TRUE, the mysql_connect() function returns the previous connection link.
Line 18 Using the new connection link ($link2), we select another database db_employee.

Use the different connection links ($link1 and $link2) above to run queries from different databases.

mysql_connect() is deprecated from PHP 5.5.0. So, it is recommended to use either PHP’s mysqli Extension or PHP Data Objects (PDO)

2. Using PHP’s mysqli Extension

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


<?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.";
}
?>

Explanation:

Line 7 We instantiate a new instance($dbh1) of MySQLi. Here, we connect with the database db_students.
Line 14 We instantiate another instance($dbh2) of MySQLi. Here, we connect with the database db_employees.

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
$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);
}
?>

Explanation:

Line 9 We instantiate a new instance($dbh1) of PDO. Here, we connect with the database db_students.
Line 15 We instantiate another new instance($dbh2) of MySQLi. Here, we connect with the database db_employees.