PDO uses 2 database connections in one query

Question

I have two different databases (I have access to both), and they have one table that has the same structure. This is a table called events . I want to connect to both databases and get the results from their events so that I can show more or less date or day.

 $db1 = new PDO('mysql:host='.$db_host1.';dbname='.$db_name1,$db_username1,$db_pass1); $db2 = new PDO('mysql:host='.$db_host2.';dbname='.$db_name2,$db_username2,$db_pass2); $db1->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); $db2->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); $today = date("Ymd"); $stmt1 = $db1->query("SELECT * FROM events WHERE event_start >= $today ORDER BY event_start ASC"); $stmt2 = $db2->query("SELECT * FROM events WHERE event_start >= $today ORDER BY event_start ASC"); //this works for only one of the database. in this case $stmt1 which connects to db1 while($row = $stmt1->fetch()) { //echo'd data will go here } 

Answer 1

The best way to do what you are trying to do is to create a third array to combine the results, and then use it. For instance:

 $db1 = new PDO('mysql:host='.$db_host1.';dbname='.$db_name1,$db_username1,$db_pass1); $db2 = new PDO('mysql:host='.$db_host2.';dbname='.$db_name2,$db_username2,$db_pass2); $db1->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); $db2->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING); $today = date("Ymd"); $query = "SELECT * FROM events WHERE event_start >= $today ORDER BY events ASC"; $stmt1 = $db1->query($query); $stmt2 = $db2->query($query); $results = array_merge($stmt1->fetchAll(PDO::FETCH_ASSOC), $stmt2->fetchAll(PDO::FETCH_ASSOC)); // Then sort the new array, perhaps something like this $events = array(); foreach ($results as $key => $row){ $events[$key] = $row['events']; } array_multisort($key, SORT_ASC, $results); print_r($results); 

I'm not sure what you are ordering in your event field; however, it is relatively easy to get a new $ results array using the same criteria. Check out PHP Array Sorting for the appropriate sorting algorithm.

Answer 2

You might want to make prepared statements. However, you can combine the results as needed after the request. Example (untested) below:

 $stmt1 = $db1->query("SELECT * FROM events WHERE event_start >= :today ORDER BY events ASC"); $stmt2 = $db2->query("SELECT * FROM events WHERE event_start >= $today ORDER BY events ASC"); $combined = array_merge($stmt1->fetchAll(PDO::FETCH_ASSOC), $stmt2->fetchAll(PDO::FETCH_ASSOC)); ksort($combined); // or some other ordering, maybe on date key? foreach($combined as $record) { // use $record['column'] access }