How can you use mySQLi to interact with multiple databases in PHP?

1 Connect to multiple databases

The first step to interact with multiple databases in PHP is to establish a connection to each one of them. You can use the mySQLi constructor function to create a new mySQLi object for each database. The constructor function takes four parameters: the host name, the user name, the password, and the database name. For example, if you have two databases named db1 and db2 on the same server, you can connect to them like this:

// Create a connection to db1
$conn1 = new mysqli("localhost", "user1", "pass1", "db1");
// Check for connection errors
if ($conn1->connect_error) {
  die("Connection to db1 failed: " . $conn1->connect_error);
}
// Create a connection to db2
$conn2 = new mysqli("localhost", "user2", "pass2", "db2");
// Check for connection errors
if ($conn2->connect_error) {
  die("Connection to db2 failed: " . $conn2->connect_error);
}        

Now you have two mySQLi objects, $conn1 and $conn2, that you can use to interact with db1 and db2 respectively.

2 Execute queries on multiple databases

The next step to interact with multiple databases in PHP is to execute queries on them. You can use the mySQLi methods to perform different types of queries, such as select, insert, update, delete, or create. To execute a query on a specific database, you need to use the corresponding mySQLi object. For example, if you want to select some data from db1 and insert it into db2, you can do something like this:

// Select some data from db1
$sql1 = "SELECT name, email FROM users WHERE status = 'active'";
$result1 = $conn1->query($sql1);
// Check if the query returned any rows
if ($result1->num_rows > 0) {
  // Loop through the rows
  while($row1 = $result1->fetch_assoc()) {
    // Extract the name and email from the row
    $name = $row1["name"];
    $email = $row1["email"];
    // Insert the name and email into db2
    $sql2 = "INSERT INTO contacts (name, email) VALUES ('$name', '$email')";
    $result2 = $conn2->query($sql2);
    // Check if the query was successful
    if ($result2 === TRUE) {
      echo "Inserted $name, $email into db2 successfully.<br>";
    } else {
      echo "Error inserting $name, $email into db2: " . $conn2->error . "<br>";
    }
  }
} else {
  echo "No active users found in db1.<br>";
}        

As you can see, you can use the same syntax and logic to execute queries on multiple databases, as long as you use the right mySQLi object for each one.

4 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?