I'm stuck and looking for some suggestions/help. I have a collection of MySQL databases. I want to query for information from each of the databases and display the results. Something like(the database names, etc are just used for examples - this is not part of a homework assignment):

Schema Name - Number of Books

"Library A" - 10
"Library B" - 15
etc.....

I am not the owner of the databases so I cannot change the fact that multiple databases were used vs using one relational database to store this information. So I've got one database per "Library" and each database has a "Books" table with "id" being one of the elements in the table - so I can use "count(id)" to get the number of books. Is there a way, using PHP, to loop through all of the databases and display the information like this? My biggest problem, I think, is that I cannot query for the database name while using the database. I have to get the database names from the Information_schema.schemata table. So I'm trying to figure out how to loop through the results of a "select schema_name from information_schema.schemata" query and for each element in the array get the count of id's in the books table. I'm new to php and mysql and am not up to speed yet on arrays. Any suggestions/help is much appreciated!

This post has been edited by kmkphp: 25 Jun, 2009 - 09:13 AM

Are they running on one machine/cluster ?

Yes - one MySQL instance - one database per customer - each database contains information specific to each customer but all of the schemas are the same (same tables names, etc).

With your current setup, try using the union or union all commands. And see if you can get the names of all the databases stored into a PHP array. Or you could create a PHP object for each database (username, dbname, password) and iterate through them. Hope this helps! Good luck!

This post has been edited by macosxnerd101: 25 Jun, 2009 - 04:56 PM