Connect to multiple MySQL databases with PHP

Within the project sometimes requires using multiple MySQL databases. It may be the existing database from another project or the new one.

To handle this with PHP requires to create separate connections for each database and use the connection accordingly while manipulating data in MySQL database.

Connect to multiple MySQL databases with PHP


Contents

  1. Configuration
  2. Manipulating Records
  3. Conclusion

1. Configuration

Create a config.php for database configuration.

Here, I am creating a connection with two databases – tutorial_db1 and tutorial_db2.

Completed Code

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname1 = "tutorial_db1"; /* Database name 1 */
$dbname2 = "tutorial_db2"; /* Database name 2 */

$conn_1 = mysqli_connect($host, $user, $password,$dbname1);
// Check connection
if (!$conn_1) {
 die("Connection failed: " . mysqli_connect_error());
}

$conn_2 = mysqli_connect($host, $user, $password,$dbname2);
// Check connection
if (!$conn_2) {
 die("Connection failed: " . mysqli_connect_error());
}

2. Manipulating Records

From the first database select all records from the posts table and insert a record in the users table in another database.

Completed Code

<?php

include "config.php";

// Select records from database 1
$select_query = "select * from posts ";
$result = mysqli_query($conn_1,$select_query);
$posts_arr = array();
while($row = mysqli_fetch_array($result)){
 $posts_arr[] = $row;
}

// Insert records in database 2
$insert_query = "insert into users(fname,lname,email) values('Yogesh','Singh','yogesh@makitweb.com')";
mysqli_query($conn_2,$insert_query);

3. Conclusion

If you want to use multiple MySQL databases in your PHP project then you need to create a separate connection variable for your databases and use variables according to the database on which you are performing an action.

If you found this tutorial helpful then don't forget to share.

2 thoughts on “Connect to multiple MySQL databases with PHP”

  1. Very comprehensive tutorial on how to set up the different connections to the different databases.
    However, you only demonstrated that one can query the databases separately. Can you further explain how to access the different databases in a same instance? For example doing a “join SQL” for two tables each from a separate database. How do you formulate this command:
    $result = mysqli_query(“which connection to use”,$join_query)?

    Reply

Leave a Comment