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

Here is the solution of 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.


Pin It
  • http://inyavic.blogspot.com Victor Inyang

    Thanks for this well detailed tutorial on
    How to Connect to Multiple MySQL Databases on a Single Web Page In PHP. Expecting more from you.

    • Neil Fin

      You welcome. Glad to know you like it. I’m working on more posts constantly.

    • neilfin

      Once again, glad to know that the tutorial helped you. Please keep an eye on this site or follow us to get informed about similar tutorials that are being posted regularly. Thank you.

  • http://www.phptrick.in neetu

    multiple databases connections can be handled by creating
    multiple variables for connection string.and call the variable wherever
    need another database connection.

    A detailed study on multiple connection handling is http://www.phptrick.in/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage/

    • jhaws1001

      Ironically, the link says “Error establishing a database connection” LOL

  • http://www.apenta.com/ Apenta

    How to connect and run query to multiple database in one time. Example: Select employee in database A and select salary from database B.
    Thanks.

  • 4motivicom

    Hello,
    Is it possible to connect databases of 2 different websites, so users can access both websites with one user and password?
    Thanks

  • http://4ella.com/ danfolt

    Hi, very nice tutorial, I have maybe a stupid question, I would like to create 2 different websites for 2 different products, lets say Real Estate and Hotel bookings, I was wondering if I can create 3 MySQL databases 1 for real estate homes, users, agencies, 2nd database for hotels, clients, hotel operators and 3rd database (read only) with GEO data as countries, provinces, regions, cities, GPS of all over the world which I would use for both websites as a informational database, the reason why is that only GEO database will be very huge having milions of records, so main purpose is for better organization, optimization. GEO database will be READ ONLY with precreated already known data (I would updated them via PHPMyAdmin only and this database should also be installed on another server.

↑ Back to Top