Query from multiple databases

Question

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 have 5 tickets open in database dogs
I have 2 tickets open in database cats

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 ?>

Never-mind this works SELECT st_id,dates,complex` FROM {$database_1}.support_ticket UNION ALL SELECT st_id,dates,complex FROM {$database_2}.support_ticket WHERE status = 'OPEN' ORDER BY dates DESC LIMIT 1 "); ` — daugaard47
– daugaard47, Commented Oct 2, 2014 at 19:08

Jay Blanchard · Accepted Answer · 2014-10-02 19:08:31Z

1

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

answered Oct 2, 2014 at 19:08

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

daugaard47 Over a year ago

yea that works, just tried it after I finished writting the question. Thanks for the help though! =)

daugaard47 Over a year ago

do you know of a better way to Select from each table. This doesn't work, but Something like this: SELECT whatever FROM dogs AND cats.support_ticket....etc

Jay Blanchard Over a year ago

Actually started to write a JOIN query, but that doesn't make sense. This is the best way to do this as you now have it without refactoring the database to have all support tickets in one database distinguished by a column containing 'cats' or 'dogs'. That is probably how I would have done it when designing the database.

daugaard47 Over a year ago

Yeah I should of planned a little more, but now I'm in to deep to change the databases around. I could, but would take forever. Thanks again for the help.

Jay Blanchard Over a year ago

Yeah. I know what you mean. We have all been there before.

Collectives™ on Stack Overflow

Query from multiple databases

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related