CODEWITHSUNDEEP
phpmysqlpdo
Hezerac
Hezerac

Reputation: 334

PHP single method for connecting to multiple databases

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.

Upvotes: 0

Views: 130

Answers (2)

Devon Bessemer
Devon Bessemer

Reputation: 35337

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');

Upvotes: 2

DanielM
DanielM

Reputation: 6666

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.

Upvotes: 2

Related Questions