selecting from two or more databases

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
123newbiephp
Forum Newbie
Posts: 4
Joined: Mon Nov 07, 2005 8:16 am

selecting from two or more databases

Post by 123newbiephp »

hi,

I have a number of different identical databases storing info about diferent regions of the world. I want to select data from lets say two databases and display it into one table/query. Both tables have the same fields. Any idea how I can do this?

i have tried the following:

select c1 from d1.t1 UNION select c1 from d2.t2 where c1='something'

the above does work, but it creates a union of the tables, rather than just getting the data and putting it into one temp table. The union would create a problem if lets say two fields were very similar (i think).

your help is much appreciated
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

What is it you want to achieve? give us som example output as it's not that clear what you are asking for.
123newbiephp
Forum Newbie
Posts: 4
Joined: Mon Nov 07, 2005 8:16 am

Post by 123newbiephp »

basically, i have 3 databases each with the same table. I want to select all the data in the 3 tables and output it to the browser. Am having issues with the mysql query to do this.

is this the right way to do it:


select * from database1.table1 where id ='$id' UNION select * from database2.table2 where id ='$id' UNION select * from database3.table3 where id ='$id'

the above does work, but is there an easier way of doing this? soon i'll be wanting to select from many more databases and the query will get very large.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

Why does the data need to be in so many different databases? can you not just have a simple identifier column?
123newbiephp
Forum Newbie
Posts: 4
Joined: Mon Nov 07, 2005 8:16 am

Post by 123newbiephp »

well mainly to keep the website easy to manage. Don't want a table with 300,000 records which will then have a big impact on speed and efficiency.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

What dbms are you using? because I seriously think you should look into database theory, design and practice. Structuring the database well with well placed indexs and there is no reason at all 300k records should cause the system to slow down (believe me I know, I spend a LOT of my time working on large datasets).
BruceT
Forum Newbie
Posts: 14
Joined: Sat Aug 27, 2005 10:23 am

Post by BruceT »

Rather than setting up separate databases with one table each in them, set up one database that has all the tables in it. Multi-table selects within one DB are simple.

But like others have said, if the content is basically the same between all the tables, you should be able to do even more combination of the fields and make it efficient. Lots of rows should not be a performance issue if you are primarily reading from them and you have proper indexes set up.
Post Reply