How to handle multiple databases with single PDO instance?

Question

I've a bit problem. I wrote a

PDO

wrapper that allow me to perform query on my database.

Now my application is multitenant, this means that I need different databases. The Data Source structure is structured like this:

(1) Main Db 



(n) Tenant DB

So I've a main database where I've all my companies, and n databases where I've login credentials and user company informations.

When my application start I call my db wrapper like so:

$this->db = new Database('host', 'name', 'user', 'pass');

whis will create an instance with my default configuration in my

index.php

. When my user logged in I store into a global variable the connection settings of him database.

Now the problem's that I need to use in some moment my config.php database credential, so I need to redeclare the database object again and again in each models method. This scenario is a pain.

What I can do for solve this?

Answer 1

If the mysql user has access to both databases, then just prepend the database name to the table name:

SELECT id,name FROM database_name.users_table

If you need to log in as a different user, then yes, you'll need to create a new PDO instance.

How to handle multiple databases with single PDO instance?

Sign up