Search code examples
phpmysqljoinpdomultiple-databases

PDO Join Query with Multple Databases in PHP

I have searched and searched for answer on this question, but have come up empty handed. I am hoping someone can point me in the direction of a solid example.

I have developed a modular system where data is stored across multiple MySQL Databases.

I am using PDO Objects to connect to the database using PHP. I need to perfom a JOIN query across two different databases but my PDO Objects are initialised for a particular Database. I have tried using the absolute reference 'dbName.tableName.field.Name' as would be performed using the deprecated mysql_query() function, but no luck.

Is it possible to perform a JOIN query across multiple databases using PDO?

Usage: I Currently have a Staff database and a Document Database. Listing all the documents returns the foreign key - StaffID. I need to translate this into a Staff name using the Staff table.

Solution

  • You have to make sure you have the same user that has access to all your databases.

    The JOIN must specify the full database.table name:

    SELECT * FROM database_1.table1 AS d1 LEFT JOIN database_2.table2 AS d2 
ON d1.someIDField = d2.someIDField;