How do you join two PDO objects in the same foreach loop?
Forgive me because I'm new to PDO. I'm not sure if there is a simple solution. I've been searching online for some time and have yet to find an answer.
I have two different databases that I'm connecting to.
try {
$db1= new PDO( "sqlsrv:server=$server;Database = $dbname", $uid, $pwd);
$db2= new PDO( "sqlsrv:server=$server;Database = $db2name", $db2uid, $pwd);
}
I'm trying to join information from a table on each database based on a common ID. I need to loop through the 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 modify this code to query both PDOs so that it can print out results in the开发者_开发百科 same foreach loop?
EDIT: I am trying to match up an ID in tableA with an ID in tableB and then print the name field in tableA next to the msg field in tableB when the IDs match.
Let's imagine (since you don't provide us your DB schema) that you have db1 with 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 common corresponding id_2, eg
array('id_1'=>1, 'name_1'=>'smth', 'message_1'=>'smth')
must be joined with array('id_2'=>1, 'name_2'=>'smth', 'message_2'=>'smth')
(as you see, id_1==id_2).
So, code you need is:
# 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'];
}
Don't query inside the foreach
condition. Query first, and join 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
}
精彩评论