3 Replies - 2444 Views - Last Post: 25 June 2009 - 05:54 PM

#1 kmkphp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 25-June 09

Query multiple databases and show results in one table

Posted 25 June 2009 - 09:22 AM

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 June 2009 - 10:13 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Query multiple databases and show results in one table

#2 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Query multiple databases and show results in one table

Posted 25 June 2009 - 11:46 AM

Are they running on one machine/cluster ?
Was This Post Helpful? 0
  • +
  • -

#3 kmkphp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 25-June 09

Re: Query multiple databases and show results in one table

Posted 25 June 2009 - 12:14 PM

View PostTrogdor, on 25 Jun, 2009 - 10:46 AM, said:

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).
Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10561
  • View blog
  • Posts: 39,071
  • Joined: 27-December 08

Re: Query multiple databases and show results in one table

Posted 25 June 2009 - 05:54 PM

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 June 2009 - 05:56 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1