CODEWITHSUNDEEP
phpsqlpdo
Azukah
Azukah

Reputation: 227

PDO use 2 database connections in one query

I have 2 different databases (I have access to both) and they have one table that has the same structure. It's a table called events. I want to connect to both databases and get results from their events so I could show if a date is greater or equal than today.

$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("Y-m-d");
$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  
}

Upvotes: 4

Views: 4004

Answers (2)

bubba
bubba

Reputation: 3847

The best way to do what yer attempting here is to create a third array to merge the results and then use that. Like so:

$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("Y-m-d");
$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 by in your events field; however, it should be relatively easy to order the new $results array by the same criteria. Check out PHP Array Sorting for an appropriate sorting algorithm.

Upvotes: 6

Rob W
Rob W

Reputation: 9142

You may want to be doing prepared statements. However, you can combine the results as needed after your query. 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
}

Upvotes: -1

Related Questions