CODEWITHSUNDEEP
mysql
Hobbyist
Hobbyist

Reputation: 16202

Selecting and adding values accorss multiple databases

-- Ramble --

I'm just running through some hypothetical setups in my head for a possible project that I may try to do whenever I have some free time. Basically I was just going to create a simple dynamic website where users can register and host their own website that's generated in PHP. I've done this before however now that I've learned quite a bit about OOP I'd like to try it again without the source being a mess.

-- End ramble --

Basically, what I'm wanting to know is how to select values across multiple databases and perform logic with them. For example, lets say I have 3 databases laid out like so:

website_1
   |_ Users
website_2
   |_Users
website_3
   |_Users

The Users table would consist of two columns for this example. (ID, Points), both of which are INT(11) NOT NULL values.

The query should return the name of the database, the amount of users, and the sum of the users points. For example:

+-----------+------------+--------------+
| Database  | user_count | total_points |
+-----------+------------+--------------+
| Website_1 |          3 |         2170 |
| Website_2 |          7 |         4020 |
| Website_3 |          2 |         1480 |
+-----------+------------+--------------+

After some research I've found that the query function "DB_NAME()" should return the name of the database, however any attempt to execute it has given me the following error:

FUNCTION website_1.DB_NAME does not exist

EDIT: DB_NAME() is for SQL SERVER :: DATABASE() returns respected results in MySQL.

And while I know how to use SELECT COUNT(ID) and SELECT SUM(Points)I'm not sure how to add the values from multiple databases. I'm a total loss of ideas here. The end result of the query I have in mind should look like this:

+-----------+------------+--------------+
| Database  | user_count | total_points |
+-----------+------------+--------------+
| Website_1 |          3 |         2170 |
| Website_2 |          7 |         4020 |
| Website_3 |          2 |         1480 |
+-----------+------------+--------------+
| Totals    |         12 |         7670 |
+-----------+------------+--------------+

Upvotes: 0

Views: 41

Answers (1)

Fenistil
Fenistil

Reputation: 3801

I think you shouldn't try to do this in pure SQL. I think the best approach would be to select the databases from the server in a PHP code (SHOW DATABASES;) then collect the databases you need to query and exec the following in a loop:

$sql = 'SELECT  "'.$database.'", COUNT(ID), SUM(Points) FROM '.$database.'.USERS;'

Print the output to a table (meanwhile you can also sum it) then print the totals.

If you wish to cache it in a table, you can create a table for it, TRUNCATE it before the loop then INSERT the results into it (INSERT SELECT..) then you can select the results from it. Then you can add some cronjob or something that in every X minutes will refresh that table. But I think these queries isn't so slow that need to be cached. (If you don't have more thousands databases).

Upvotes: 1

Related Questions