20

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.

hablema
  • 540
  • 1
  • 5
  • 17

6 Answers6

31

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Laz
  • 337
  • 3
  • 5
  • 2
    Of course that does not work if you have a database account which has only rights to use one database. And a lot of accounts have this for security reasons. I made a simple switch witch checks what database you want to use when calling the class and when you call for example db1 it gets the account information form a config file. – botenvouwer May 03 '13 at 10:30
  • 1
    Also, this is not necessarily safe to use for inserting or updating when you're using replication. – Chris Baker Dec 04 '13 at 06:25
  • doesn this switch between *schemas* and not *databases*? – Frank Conry Nov 11 '16 at 17:25
  • 1
    Throws "Base table or view not found" the error message shows "connection_Initiated_DataBase.attempted_To_Switch_To_Database.table"... it implies that the PDO connection always expects you to be with the same database after initializing the connection. – me_ Dec 23 '17 at 02:38
  • @FrankConry "database" is another name for "schema" -- the terms are usually equivalent and interchangeable, though "schema" might be more technically specific and correct. – Aaron Wallentine Feb 23 '18 at 23:16
  • @AaronWallentine Not true. In MySQL (at least last I used it) they were equivilant but in a normal RDMS they are not. See here: https://stackoverflow.com/questions/5323740/difference-between-database-and-schema – Frank Conry Feb 25 '18 at 16:40
21

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());
BlindAndFurious
  • 698
  • 4
  • 10
  • 6
    I can confirm this will work in MySQL, provided the user connected has privileges to the new database. – Mahn Jan 07 '15 at 23:45
10

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";
Matthew
  • 131
  • 1
  • 3
  • this throws "No Database Selected" with the query "SELECT * FROM `database.table`" – me_ Dec 23 '17 at 02:13
  • Found this answer to not be true in my case. `SELECT * FROM database1.table1`, results in error "No database selected". – David Nov 01 '18 at 15:33
8

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Ingmar Boddington
  • 3,440
  • 19
  • 38
6

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

 USE db_name

and then write your requests

shotex
  • 349
  • 5
  • 10
  • I also had trouble with this approach using PDO ... would get an error #2014, "Cannot execute queries while other unbuffered queries are active." Even though I had used fetchAll() and/or closed the cursor on all my other queries. I ended up having to use the approach of having separate PDO instances for each DB/schema, then it worked perfectly. – Aaron Wallentine Feb 23 '18 at 23:51
  • This works in code for me, when leaving `dbname=xxx;` out of the PDO initializer. – David Nov 01 '18 at 15:29
0

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

hasan darwish
  • 117
  • 1
  • 6