Querying multiple databases?

mdowling

I am developing a system to use with several websites. I have a database for each client that I am having utilize my system. In each database is a table called members.

Some backstory as to what I'm doing...

All the accounts using my system have information added to a table in the database main called clients. There I store the index of the client, the name of the client, the database of the client, etc...

What I need to be able to do....

So, I want to be able to query all the member tables of all the databases. From there I can do statistical analysis of my members data, select a random member, or whatever, but I need all the member table records from all the databases in the same array (from a mysql query).

What would be the best approach to this? A sort of SELECT * FROM members for all member tables in all databases. Note, I can form a list of databases if need be.

Or, I could just make all the member data in the main database of my system and attach a client ID to each member. The only drawback to this is modifying thousands of lines of code and modifying my five current websites that use this system to follow the new table rules, update their indexes, update everything that uses the member's index as a foriegn key, etc...

So, the first approach would be much more preferable.

liquorvicar

Your first option should be eminently possible, e.g

SELECT * FROM `db1`.`members`
SELECT * FROM `db2`.`members`
SELECT * FROM `db3`.`members`

You could union these queries if you had a recent enough version of MySQL (i.e >=4). You would need to make sure that your db user had at least read permissions across all the members tables.

jheitz

It might be too late now, as you point out, but having a DATABASE per member is, in my view a very bad idea. For one, if you need to change any Stored Procedure, table structure, index, etc... you'll need to manually do this on every single database simultaneosly.

My advice: If this is a long term and live project, stop and really analyze how difficult is to revert to a single database (it will be much more painful later).

If you still want to go for the first option: Liquorvicar solution is fine. Not being an expert in MySQL I am not sure how easy is it to implement that approach in a dynamic way (ie: so that if you add a new member you don't need a new SELECT, the SELECTS are generated using the database name from the table). On SQL Server you could only do this using Dynamic SQL.

Good luck