PHP single method for connecting to multiple databases

Question

My question is given the following code how can I use a single method to connect to multiple databases?

Array of config settings:

$GLOBALS['config'] = array(
'mysql' => array(
    'host' => '127.0.0.1',
    'username' => 'root',
    'password' => '',
    'db' => array(
        'db1' => 'database1',
        'db2' => 'database2'
    )
)

Connect to database:

private function __construct() {
    try {
        $this->_pdo = new PDO('mysql:host=' . Config::get('mysql/host') . ';dbname=' . Config::get('mysql/db/db1'), Config::get('mysql/username'), Config::get('mysql/password'));
    } catch(PDOException $e) {
        die($e->getMessage());
    }
}

So instead of hard coding db1 into the connection:

dbname=' . Config::get('mysql/db/db1')

How to not specify a specific database here but allow for one to be called for later? I am trying to make a reusable class that can connect to any databases that are added to the config array.

What are you asking exactly, how to connect to multiple databases or how to leave a connection open? — jeroen, Apr 20 '15 at 16:55
possible duplicate of [Switch between multiple database in PDO](http://stackoverflow.com/questions/9588775/switch-between-multiple-database-in-pdo) — Devon Bessemer, Apr 20 '15 at 16:58

score 2 · Answer 1 · edited May 23 '17 at 12:06

2

PDO doesn't provide functionality for switching databases.

You can still run a query against another database by prefixing the database to the table name, assuming the same user has access to it.

$pdo->query('SELECT * FROM database2.table');

You can also execute the use statement as confirmed here: Switch between multiple database in PDO

$pdo->exec('USE database2');
$pdo->query('SELECT * FROM table');

edited May 23 '17 at 12:06

Community

1
1

answered Apr 20 '15 at 16:51

Devon Bessemer

34,461
9
69
95

1

I'd advise against the using `USE` as you'll end up needing to use it every time just to make sure you're using the correct one. You can also create multiple PDO objects. – DanielM Apr 20 '15 at 16:58
I agree with that. Prefixing (or multiple objects) is definitely the better solution. It also would be the only way if you have a query that goes across two databases. – Devon Bessemer Apr 20 '15 at 17:00

score 2 · Answer 2 · answered Apr 20 '15 at 17:01

2

You could create multiple pdo connections:

private function __construct() {
    try {
        $this->_pdo1 = new PDO('mysql:host=' . Config::get('mysql/host') . ';dbname=' . Config::get('mysql/db/db1'), Config::get('mysql/username'), Config::get('mysql/password'));
        $this->_pdo2 = new PDO('mysql:host=' . Config::get('mysql/host') . ';dbname=' . Config::get('mysql/db/db2'), Config::get('mysql/username'), Config::get('mysql/password'));
    } catch(PDOException $e) {
        die($e->getMessage());
    }
}

This is pretty good for sharding as what might start out as different schema's might become different servers.

answered Apr 20 '15 at 17:01

DanielM

6,380
2
38
57

Hi thanks for the reply. I think your response is an answer closest to what I was looking for. I just wonder if there will be trouble down the road with this approach is if there are many databases: – Hezerac Apr 20 '15 at 17:23
1

Presumably you need to manage which connections point at which schemas anyway. You could make an abstract for a single PDO manager, extend that into Singletons for each DB, then use a static factory to create or retrieve the Singletons. – DanielM Apr 20 '15 at 18:28

PHP single method for connecting to multiple databases

2 Answers2