How can I query two different databases in a single query using MySQL?

Question

I have two websites that post news information. I just got a request to make a single page for one of the sites that shows the mixed results from both sites. They are located on the same server and the tables are close to identical, but I am having problems figuring out how to combine the two queries. The postings need to be sorted by date, title when all is said and done.

For arguments sake, a simplified form of the tables is

DB 1, TABLE Clips1 (PK_ID, DateToShow, Title, Description)

DB 2, TABLE Clips2 (PK_ID, DateToShow, Title, Description)

So the single query would hit both databases (usernames and passwords are different currently), or two queries and some kind of combination that would keep the date and titles sorted.

Some more information, the reason the sites are different is that one is Spanish and one is English and we wanted them as two completely different sites (most of the content is completely different), however the request is to post the English stuff as well to add more content to the Spanish version. If I cannot do it with a simple combine, I am afraid I will have to modify the English site's admin to post database records to both areas, which seems messy to me. — RiddlerDev
– RiddlerDev, Commented Feb 24, 2010 at 4:34

hobodave · Accepted Answer · 2010-02-24 04:34:44Z

1

IF you can use a single connection for the query, it is trivial.

You say that the schemas are on the same server, so with the appropriate permissions this shouldn't be a problem.

The general syntax for this is:

SELECT schemaA.table.column
FROM schemaA.table
UNION
SELECT schemaB.table.column
FROM schemaB.table

You can even do JOINs, just remember to specify the appropriate schema for every table and field reference.

answered Feb 24, 2010 at 4:34

hobodave

29.4k4 gold badges74 silver badges77 bronze badges

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

4 Comments

RiddlerDev Over a year ago

I have complete control over the server, so I assume it is as simple as creating a joint username/password attached to both databases to use?

hobodave Over a year ago

You could do that, or simply give an existing user the permissions to the other DB. e.g. GRANT ALL ON `spanish_db`.* TO 'english_user'@'somehost' IDENTIFIED BY 'english_pass'

Dr. Rajesh Rolen Over a year ago

dear hobodave.. i was just edting my answere and when i came back after editing my answer .. u gave me a negative point..

RiddlerDev Over a year ago

perfect @hobodave, I was wondering if I could do something like that. Thanks a bunch!

Collectives™ on Stack Overflow

How can I query two different databases in a single query using MySQL?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related