0

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.

Improve this question
2
  • 1
    What are you asking exactly, how to connect to multiple databases or how to leave a connection open? Commented Apr 20, 2015 at 16:55
  • possible duplicate of Switch between multiple database in PDO Commented Apr 20, 2015 at 16:58

2 Answers 2

Reset to default
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.

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

2 Comments

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:
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.
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');
Improve this answer

2 Comments

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.
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.