CODEWITHSUNDEEP
phpmysqlpdo
dlofrodloh
dlofrodloh

Reputation: 1744

How to run a query with a join accross two databases with PDO

I've got two PDO objects representing two databaes, pdo1 and pdo2, declared as per the following:

try {
    $pdo1 = new PDO('mysql:host=localhost;dbname=database1', 'user', 'password');
    $pdo1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo1->exec('SET NAMES "utf8"');
}

^ And I've done the same for $pdo2 which is for the second database.

The way I've been accessing the database so far has been like the following:

try {
     $sql   = 'SELECT * from table1';
     $result = $pdo1->query($sql);  
}

Now I want to run a query where two tables are joined across two databases. How can I do this with the two pdo objects? Here's what I've got, but I'm stuck on how to do the $result part where I've put asterixis:

try {
     $sql   = 'SELECT database1.table1.name FROM database1.table1 LEFT JOIN database2.table2
               ON database1.table1.userid = database2.table2.userid';
     $result = $pdo***->query($sql);    
     }

Previously, it would refer to either $pdo1 or $pdo2, but how do you combine that part to pull the result from combined pdo objects?

Upvotes: 1

Views: 2908

Answers (1)

Rick James
Rick James

Reputation: 142518

Use one connection, then do a query like

SELECT ... FROM db1.tbl1 JOIN db2.tbl2 ...

(You will need permissions to access both databases.)

Upvotes: 3

Related Questions