How do you join two PDOs in the same foreach loop?

Question

Forgive me because I'm new to PDO. I am not sure if there is a simple solution. I searched the Internet for a while and have not yet found an answer.

I have two different databases that I connect to.

try { $db1= new PDO( "sqlsrv:server=$server;Database = $dbname", $uid, $pwd); $db2= new PDO( "sqlsrv:server=$server;Database = $db2name", $db2uid, $pwd); } 

I am trying to combine information from a table in each database based on a common identifier. I need to view information to print a list.

 $sql= "SELECT tableA.name, tableB.messages FROM tableA INNER JOIN tableB ON tableA.id = tableB.id"; foreach ($db1->query($sql) as $row) { //HOW CAN I QUERY DB2?? $id = $row['id']; $name = $row['name']; $msg= $row['messages']; echo $name . "etc..."; } 

How can I change this code to request both PDOs so that it can print the results in the same foreach loop?

EDIT: I am trying to match the ID in table A with the identifier in table B, and then print the name field in tableA next to the msg field in tableB when the identifiers match.

Answer 1

Imagine (because you do not provide us with your database schema) that you have db1 with a table

 Db1table id_1 name_1 message_1 

and db2 with table

 Db2table id_2 name_2 message_2 

And each id_1 refers to a common corresponding id_2, for example array('id_1'=>1, 'name_1'=>'smth', 'message_1'=>'smth') should be connected to array('id_2'=>1, 'name_2'=>'smth', 'message_2'=>'smth') (as you can see, id_1 == id_2).

So you need the code:

 # make a "indexed" hash by common id column for fast access $hash_by_id=array(); foreach($db2->query($sql2) as $row2){ $hash_by_id[$row2['id_2']]=$row2; } foreach($db1->query($sql1) as $row1){ $joined_row2=$hash_by_id[$row1['id_1']]; #pseudo-join echo $joined_row2['id_2']==$row1['id_1'] # true # from db1 echo $joined_row2['id_2']; echo $joined_row2['name_2']; echo $joined_row2['message_2']; # from db2 echo $row1['id_1']; echo $row1['name_1']; echo $row1['message_1']; } 

Answer 2

Do not query inside the foreach clause. First submit a query and append the results:

 // Assuming your two queries are $sql1 and $sql2 // Unless both connections use the same SQL statement... $res1 = $db1->query($sql1); $res2 = $db2->query($sql2); $results = array_merge($res1, $res2); foreach ($results as $row) { // echo the results }