A Comprehensive Guide to Connecting Multiple MySQL Databases on a Single PHP Webpage

Introduction

In web development, data is often distributed across multiple MySQL databases. This article explains how to connect and query from several databases within a single PHP script, ensuring data integrity and security through step-by-step examples.

Using the Deprecated mysql_connect Method

The mysql_connect function was historically used for database connections in PHP, but it is deprecated since PHP 5.5 and removed in PHP 7.0. To connect to multiple databases, call mysql_connect multiple times. If connection parameters are identical, set the $new_link parameter to true to force a new connection.

$dbh1 = mysql_connect($hostname, $username, $password);
$dbh2 = mysql_connect($hostname, $username, $password, true);
mysql_select_db('database1', $dbh1);
mysql_select_db('database2', $dbh2);

Specify the connection identifier when executing queries:

$result1 = mysql_query('SELECT * FROM table1', $dbh1);
$result2 = mysql_query('SELECT * FROM table2', $dbh2);

Without the identifier, the last connection is used. This method is not recommended due to security vulnerabilities and lack of support in modern PHP versions.

Using the MySQLi Method

MySQLi (MySQL Improved) is an enhanced extension supporting both procedural and object-oriented programming, offering better security and efficiency than the old MySQL extension.

Procedural approach:

$link1 = mysqli_connect('localhost', 'username', 'password', 'database1');
$link2 = mysqli_connect('localhost', 'username', 'password', 'database2');
if (!$link1 || !$link2) {
    die('Connection failed: ' . mysqli_connect_error());
}

Object-oriented approach:

$mysqli1 = new mysqli('localhost', 'username', 'password', 'database1');
$mysqli2 = new mysqli('localhost', 'username', 'password', 'database2');
if ($mysqli1->connect_error || $mysqli2->connect_error) {
    die('Connection failed: ' . $mysqli1->connect_error . ' or ' . $mysqli2->connect_error);
}

Queries are handled similarly, with error handling recommended for robustness.

Using the PDO Method

PDO (PHP Data Objects) is a database access layer providing a uniform interface for multiple databases, supporting prepared statements to prevent SQL injection and enhance security.

try {
    $pdo1 = new PDO('mysql:host=localhost;dbname=database1', 'username', 'password');
    $pdo2 = new PDO('mysql:host=localhost;dbname=database2', 'username', 'password');
    $pdo1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Use prepared statements for queries:

$stmt = $pdo1->prepare('SELECT * FROM table WHERE id = :id');
$stmt->execute(['id' => 1]);
$row = $stmt->fetch();

PDO allows multiple connections and is the recommended approach for new projects.

Comparison and Best Practices

Security: PDO and MySQLi support prepared statements, reducing SQL injection risks; the old MySQL extension does not.

Performance: Multiple connections may add overhead; if databases are on the same host and accessible, consider using a single connection with database name prefixes in queries.

Recommendation: Prefer PDO for flexibility and security, or MySQLi for specific MySQL features; avoid the deprecated MySQL extension.

Conclusion

Connecting to multiple MySQL databases in PHP is achievable through various methods, with PDO being the most modern and secure. Always implement error handling and adhere to up-to-date PHP practices for reliable applications.