Switch between multiple database in PDO

I’m new to PDO. I would like to know if there is anything similar to mysql_select_db in PDO, so that i can switch between different databases during runtime without the need for creating a new object.

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

I know that I am a couple of months late but you should be able to switch between databases from within your query.

examples:

$sql = "SELECT * FROM dbname.tablename";

$sql = "SELECT * FROM anotherdbname.anothertablename"

So even if your original $pdo object was used ‘blahblah’ as the dbname, you should still be okay based on the select examples I provided.

Solution 2

It looks like PDO does not have database switching because not every database engine supports it.

AFAIK PostgreSQL does not have database switching, but offer schemas and u can switch between those.

However if you’re using mysql check if this works for you:

$pdo = new PDO('mysql:dbname=db1;host=127.0.0.1','user','pass');

$sql = 'select count(*) from table_name';

$res = $pdo->query($sql);
print_r($res->fetchAll());

$pdo->exec('USE db2');

$res = $pdo->query($sql);
print_r($res->fetchAll());

Solution 3

You actually do not need to specify the database upon connection at all. As long as you specify the database in every query, as Laz stated, this will work:

$dbh = new PDO('mysql:host=127.0.0.1','USER','PASS');

$query = "SELECT * FROM database1.table1";
$query = "SELECT * FROM database2.table1";

Solution 4

There is not, you will need to create two PDO objects for the separate connections if you would like to use both at runtime.

Edit: Interesting point by @laz below (which I’m guessing is the cause of negative votes on my answer). I was thinking under the assumption that the databases were on separate servers tbh, in which case my answer stands.

Solution 5

you don’t even need to specify the database in every query, just use msyql syntax

 USE db_name

and then write your requests

Solution 6

you can make this :

$database1 = new PDO("mysql:host=localhost;dbname=db1;charset=utf8;",$username, $password);
$database2 = new PDO("mysql:host=localhost;dbname=db2;charset=utf8;",$username, $password);

simple 😀

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply