Connect multiple databases in php

If you use PHP5 (And you should, given that PHP4 has been deprecated), you should use PDO, since this is slowly becoming the new standard. One (very) important benefit of PDO, is that it supports bound parameters, which makes for much more secure code.

You would connect through PDO, like this:

try {
  $db = new PDO('mysql:dbname=databasename;host=127.0.0.1', 'username', 'password');
} catch (PDOException $ex) {
  echo 'Connection failed: ' . $ex->getMessage();
}

(Of course replace databasename, username and password above)

You can then query the database like this:

$result = $db->query("select * from tablename");
foreach ($result as $row) {
  echo $row['foo'] . "\n";
}

Or, if you have variables:

$stmt = $db->prepare("select * from tablename where id = :id");
$stmt->execute(array(':id' => 42));
$row = $stmt->fetch();

If you need multiple connections open at once, you can simply create multiple instances of PDO:

try {
  $db1 = new PDO('mysql:dbname=databas1;host=127.0.0.1', 'username', 'password');
  $db2 = new PDO('mysql:dbname=databas2;host=127.0.0.1', 'username', 'password');
} catch (PDOException $ex) {
  echo 'Connection failed: ' . $ex->getMessage();
}

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.

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.

 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo $row['id']. ' - ' . $row['name']. ' - ' . $row['email']. '
';
    }
} 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.

 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo $row['id']. ' - ' . $row['name']. ' - ' . $row['designation']. '
';
    }
} else {
    echo 'No records found!';
}
?>

Step-4) Closing the Connection

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

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.

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']. '
';
    }
}
catch(PDOException $e) {
    die('Error ' . $e->getMessage());
}
?>

Step-2) Connect the Second Database

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']. '
';
    }
}
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.

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:)

Can we connect multiple database in PHP?

How can I connect one database to another in PHP?

How do I link two databases together?

Is it possible to connect to multiple databases simultaneously?