PDO and multiple schemas

Question

I have two databases that share user credentials. I'm using PDO. Does this means that I have to create two PDO connections, one for each database? I checked the docs, the dsn stands only for one dbname.

Answer 1

No, you'd only need two connections if the DBs you're accessing are in physically distinct databases instances (different server machines or different database copies), or are using DIFFERENT credentials for the two databases.

The DB name you specify in the DSN is merely the default database, but you can trivially do

SELECT fieldname FROM schema.table ...

to override that default. e.g.

SELECT fieldname FROM table

is the equivalent of

SELECT fieldname FROM default_specified_in_connection_string.table

Answer 2

Yes, and this is not PDO-specific. Every MySQL client connection, no matter in what language, works on (at most) one database. If you want to change the database, you must use USE newDatabaseName. But that of course makes no sense with PDO, so simply create two connections.

EDIT:

However, as Marc B pointed out, you can use a prefix to reference a different database on the same MySQL server. But this is not really handy, and I would suppose that it can cause problems when you have connection specific settings (for example, a certain charset that is not compatible with the other DB).

Answer 3

Depends what you mean by "database". Some people use the term to refer to a MySQL instance. Each PDO connection can reference only one MySQL instance.

But some people use the term database as a synonym for "schema". There can be many schemas on a single MySQL instance, and you can use a single PDO connection to access any schema. Use one of the following solutions:

• Use qualified table names as @MarcB shows
• Execute the statement USE schema_name to change the default schema (see my answer to How do I select a MySQL database to use with PDO in PHP?)

Another exception is if the user/password you use to connect to the MySQL instance has been granted privileges only on one of the schemas you want to use. Then you need to reconnect with a different user/password to access other schemas.

Answer 4

Yes, you must setup a connection to each database.