CODEWITHSUNDEEP
phpmysql
daugaard47
daugaard47

Reputation: 1868

Query from multiple databases

I have a ticket system I set up for multiple websites. Each table in the database is the same on all website.

My question is how would I query the multiple tables from each database?

Example:

I need to write a query that will tell me what is the most recent ticket submitted.

So I want to know where the ticket came from and the time it was posted.

Here is what I have tried but need some guidance as I've never worked with multiple databases before.

$database_1 = 'dogs';
$database_2 = 'cats';

$recent = DB::getInstance()->query("
SELECT `st_id`,`dates`,`complex` FROM {$database_1}.`support_ticket` WHERE `status` = 'OPEN' ORDER BY `dates` DESC LIMIT 1
UNION ALL
SELECT `st_id`,`dates`,`complex` FROM {$database_2}.`support_ticket` WHERE `status` = 'OPEN' ORDER BY `dates` DESC LIMIT 1 ");

foreach($recent->results() as $r):
?>
<div class="box-bottom">Most Recent: <?php echo escape($r->complex); ?> - 
<?php echo escape (date("F d, Y - h:i a", strtotime ($r->dates))); ?></div>
<?php endforeach ?>

Upvotes: 0

Views: 55

Answers (1)

Jay Blanchard
Jay Blanchard

Reputation: 34416

Just use one ORDER BY statement

SELECT `st_id`,`dates`,`complex` 
FROM {$database_1}.`support_ticket` 
WHERE `status` = 'OPEN' 
UNION ALL
SELECT `st_id`,`dates`,`complex` 
FROM {$database_2}.`support_ticket` 
WHERE `status` = 'OPEN' 
ORDER BY `dates` DESC LIMIT 1

Upvotes: 1

Related Questions