by Alex
Sometimes you want to work with two or more SQL servers in the same PHP script.
For example, you may want to copy data from one SQL server to another, or compare the data among the two.
You may also want to keep two separate connections to the same SQL server for security purposes.
In this case, the idea is to use two SQL accounts with different privileges to minimize the risk of injection attacks.
(I explain exactly how this works in the “Database permissions” chapter of my Security course).
Fortunately, PHP lets you keep multiple MySQL connections open at the same time.
Let’s see how you can do that with both MySQLi and with PDO.
How to connect to multiple MySQL servers with MySQLi
Let’s begin with MySQLi.
You can connect to a MySQL server with MySQLi like this:
$dbHost1 = '192.168.0.1';
$dbUser1 = 'user_1';
$dbPass1 = 'password_1';
$mysql1 = mysqli_connect($dbHost1, $dbUser1, $dbPass1);
mysqli_select_db($mysql1, 'my_db_1');
(Of course, you should use more secure passwords than “password_1”. Here’s how to generate secure passwords).
If you want to connect to a different MySQL server without closing the first connection, you can simply create a new MySQLi connection resource variable with the parameters of the new database, without affecting the other one.
For example:
/* First connection */
$dbHost1 = '192.168.0.1';
$dbUser1 = 'user_1';
$dbPass1 = 'password_1';
$mysql1 = mysqli_connect($dbHost1, $dbUser1, $dbPass1);
mysqli_select_db($mysql1, 'my_db_1');
/* Second connection */
$dbHost2 = '192.168.0.2';
$dbUser2 = 'user_2';
$dbPass2 = 'password_2';
$mysql2 = mysqli_connect($dbHost2, $dbUser2, $dbPass2);
mysqli_select_db($mysql2, 'my_db_2');
You can then use both connections at the same time:
$query = 'SELECT * FROM users';
/* Read users from first server */
$result = mysqli_query($mysqli1, $query);
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
echo 'Users on server 1:';
foreach ($rows as $row) {
echo $row['user_name'] . '<br>';
}
/* Read users from second server */
$result = mysqli_query($mysqli2, $query);
$rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
echo 'Users on server 2:';
foreach ($rows as $row) {
echo $row['user_name'] . '<br>';
}
This example has two connections only, but you can create as many connections as you want.
Here’s how to connect to two SQL server using MySQLi’s OOP syntax:
/* First connection */
$dbHost1 = '192.168.0.1';
$dbUser1 = 'user_1';
$dbPass1 = 'password_1';
$mysql1 = new mysqli($dbHost1, $dbUser1, $dbPass1, 'my_db_1');
/* Second connection */
$dbHost2 = '192.168.0.2';
$dbUser2 = 'user_2';
$dbPass2 = 'password_2';
$mysql2 = new mysqli($dbHost2, $dbUser2, $dbPass2, 'my_db_2');
/* Run a query on first server */
$mysql1->query('DELETE FROM users WHERE id = 1');
/* Run a query on second server */
$mysql2->query('DELETE FROM users WHERE id = 2');
How to connect to multiple MySQL servers with PDO
You can create multiple connections to different MySQL servers with PDO too.
The same concept applies: just use a different resource variable for each connection.
For example:
/* First connection */
$dbHost1 = '192.168.0.1';
$dbUser1 = 'user_1';
$dbPass1 = 'password_1';
$mysql1 = new PDO('mysql:host=' . $dbHost1 . ';dbname=my_db_1', $dbUser1, $dbPass1);
/* Second connection */
$dbHost2 = '192.168.0.2';
$dbUser2 = 'user_2';
$dbPass2 = 'password_2';
$mysql2 = new PDO('mysql:host=' . $dbHost2 . ';dbname=my_db_2', $dbUser2, $dbPass2);
/* Run a query on first server */
$mysql1->exec('DELETE FROM users WHERE id = 1');
/* Run a query on second server */
$mysql2->exec('DELETE FROM users WHERE id = 2');
I hope you enjoyed this little tutorial.
To learn more about PHP and MySQL: How to use PHP with MySQL: the complete tutorial
Leave a comment below if you have any questions.
Alex