Two PDO DB connections for two different MySQL servers in the same script

Viewed 76

Until now I used one MySQL server to which I was connection through including the following code from a file like i.e. pdo-config.php

<?php
$host = 'server';
$db   = 'db';
$user = 'user';
$pass = 'pass';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>

I would like to connect to a second MySQL server in the future with the same method and use both connections in the same PHP scripts. In order to not mix up things I was thinking to change the second connection parameters in the way that I would add a "2" at the end of every variable name like following:

<?php
$host2 = 'server2';
$db2   = 'db2';
$user2 = 'user2';
$pass2 = 'pass2';
$charset2 = 'utf8mb4';

$dsn2 = "mysql:host=$host2;dbname=$db2;charset=$charset2";
$options2 = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo2 = new PDO($dsn2, $user2, $pass2, $options2);
} catch (\PDOException $e2) {
     throw new \PDOException($e2->getMessage(), (int)$e2->getCode());
}
?>

Is this the right way to address this or is there a better way recommended to handle two DB connections kind of simultaneously?

The code for the connection is from this website: https://phpdelusions.net/pdo

1 Answers

That is OK.

Each database should have exactly one connection in the script. The PDO instance is created once and then passed on where it is needed. Normally, the database connection does not need to be closed in the Script, this happens automatically at the end of the script.

It is also possible to build multiple connections to the same database. This is not good, it causes problems and it is a waste of resources.

PDO also allows multiple connections to databases of different types. So for example a connection to MySql and to SQLIte-DB.

Related