Connecting to more than one Database (PDO)

PHP
1

I have read it’s possible to do a join between tables in different databases. But what I don’t understand is how to make the database connection(s). I know I could make two PDO objects for either database. But how then would I use both objects when running a query?
Can anyone explain how this is done?
I have had a search around, but all I found was old stuff about mysql_*, I’m using PDO.

2

Mysql supports joins across two databases provided that both database are on the same server and have the same access credentials. All you do is add the database name as a prefix to your table names.

SELECT * FROM db1.users LEFT JOIN db2.user_profiles ...

And of course can drop the prefix for whichever database was used when creating the connection.

Needless to say, use with caution. Personally, I would rather open two connections and execute two queries.

1 Like
3

The answer is simple and irrelevant to the API you are using.

You can always address a table from a different database, as long as a current use has the access to it. for example, given there are two databases, sales and marketing, both contain events table and the DB user has access to both the following code would work all right

SELECT * FROM sales.events e1 JOIN marketing.events e2 ON e1.date=e2.date

In case the current user don’t have the access to the second database, there is no way to make a cross-database query.

1 Like
4

So I don’t need to make two connections for this, so long as the user has sufficient privileges for both databases?

I was working on that in the meantime, but thought the join would be more efficient.

5

Correct. One connection is all you need. I suppose you could make a second connection just to test the credentials but you don’t need to.

And again, as far as I know, mysql is the only major database that supports this. If you do any testing using say sqlite then this approach will not work.

6

I wasn’t aware of that, I assumed that the connection object could only access the database specified in the connection.
So it’s a lot simpler than I thought it was (very rare).

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.