CODEWITHSUNDEEP
phpmysql
RiddlerDev
RiddlerDev

Reputation: 7449

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

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.

Upvotes: 1

Views: 753

Answers (1)

hobodave
hobodave

Reputation: 29303

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.

Upvotes: 1

Related Questions