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
– jeroen, Commented Apr 20, 2015 at 16:55
possible duplicate of Switch between multiple database in PDO — Devon Bessemer
– Devon Bessemer, Commented Apr 20, 2015 at 16:58

DanielM · Accepted Answer · 2015-04-20 17:01:09Z

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, 2015 at 17:01

DanielM

6,7262 gold badges42 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hezerac Over a year ago

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:

DanielM Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:06:21Z

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, 2017 at 12:06

CommunityBot

11 silver badge

answered Apr 20, 2015 at 16:51

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

2 Comments

DanielM Over a year ago

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.

Devon Bessemer Over a year ago

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.

Collectives™ on Stack Overflow

PHP single method for connecting to multiple databases

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related