Connect to Multiple Databases with PHP MySQLi and PDO

Hi! In this tutorial, we'll see how to connect to multiple databases using PHP's MySQLi and PDO (PHP Data Object) library. At times you may want to work with multiple mysql databases in the same context. Therefore, you need to connect to two or more databases in the same mysql instance and fetch data from them simultaneously. The old 'MySQL' extension has been deprecated since PHP5.5, so I would recommend you to use 'MySQLi' or even better 'PDO', which provides an additional layer of security and supports multiple databases such as MySQL, MSSQL Server, PostgreSQL, Oracle and much more.

The databases can be on the same server as php script or on different server. No matter what, connecting php to remote mysql server is similar to the one on the same server, except that you must provide permission to access the remote database.

Connecting Multiple Databases with PHP MySQLi:

Establishing connection to multiple databases using mysqli api is easier than doing it with pdo. All you need to do is open a single connection to the server, and switch between different databases on go with the mysqli_select_db() method.

Consider this scenario, we have two databases, one is 'mydatabase_1' with a table 'mytable_1' and another is 'mydatabase_2' with a table 'mytable_2'. Now we will connect and fetch data from these two databases with in the same php script.

Step-1) Open the Mysql Connection

Use the mysqli_connect() method to open connection to database server without specifying the name of the database.

<?php
$hostname = 'localhost';
$username = 'my_username';
$password = 'my_password';
$con = mysqli_connect($hostname, $username, $password);
if(!$con){
    die('Error ' . mysqli_connect_error());
}
?>

Step-2) Select and Retrieve Records from the First Database

Next, select the database 'mydatabase_1' with the help of mysqli_select_db() method and display records from it as usual.

<?php
mysqli_select_db($con, 'mydatabase_1');

$sql = 'select id, name, email from mytable_1';
$result = mysqli_query($con, $sql);
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo $row['id']. ' - ' . $row['name']. ' - ' . $row['email']. '<br>';
    }
} else {
    echo 'No records found!';
}
?>

Step-3) Select and Retrieve Records from the Second Database

Now switch over to the second database and get records from it in the same way.

<?php
mysqli_select_db($con, 'mydatabase_2');

$sql = 'select id, name, designation from mytable_2';
$result = mysqli_query($con, $sql);
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo $row['id']. ' - ' . $row['name']. ' - ' . $row['designation']. '<br>';
    }
} else {
    echo 'No records found!';
}
?>

Step-4) Closing the Connection

When you have finished, you must close the mysql connection in this way.

<?php
mysqli_close($con);
?>

Connecting Multiple Databases with PHP PDO:

With PDO, things work little differently. You can't use the single connection to work with multiple databases here. Because, PDO requires that you provide the name of the database when connecting to the database server. It uses a data source name (DSN) and requires you to select the database via the constructor method.

Step-1) Connect First Database with PDO

We must create different pdo objects for different database connections and include the script inside the try{} catch{} block. It also supports prepared statements with named parameters, thus reducing the possibility of sql injection.

<?php
try {
    $pdo1 = new PDO('mysql:host=localhost;dbname=mydatabase_1', 'my_username', 'my_password');
    $pdo1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $pdo1->prepare('select id, name, email from mytable_1');
    $stmt->execute();
    foreach ($stmt->fetchAll() as $row) {
        echo $row['id']. ' - ' . $row['name']. ' - ' . $row['email']. '<br>';
    }
}
catch(PDOException $e) {
    die('Error ' . $e->getMessage());
}
?>

Step-2) Connect the Second Database

<?php
try {
    $pdo2 = new PDO('mysql:host=localhost;dbname=mydatabase_2', 'my_username', 'my_password');
    $pdo2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $pdo2->prepare('select id, name, designation from mytable_2');
    $stmt->execute();
    foreach ($stmt->fetchAll() as $row) {
        echo $row['id']. ' - ' . $row['name']. ' - ' . $row['designation']. '<br>';
    }
}
catch(PDOException $e) {
    die('Error ' . $e->getMessage());
}
?>

Step-3) Close Connection

While closing the connection, you must close all the opened connection. Since we have created two pdo objects, you have to set both as null.

<?php
$pdo1 = null;
$pdo2 = null;
?>

Although working with pdo seems little complex than mysqli, it is very beneficial to go with it since you can switch over to a different database system in the future with minimal changes.

Read Also:

• How to Create Database CRUD Operations using PHP MySQL
• AJAX Country, State and City Dropdown List in PHP MySQL

Likewise you can connect to multiple databases in php using mysqli and pdo extension. Hope this is useful to you. Please share the post on your social circle if you like it. Good day:)