Summing a field from all tables in a database

Question

Hello,

I have a MySQL database called "bookfeather." It contains 56 tables. Each table has the following structure:

id site votes_up votes_down

The value for "site" is a book title. The value for "votes_up" is an integer. Sometimes a unique value for "site" appears in more than one table.

For each unique value "site" in the entire database, I would like to sum "votes_up" from all 56 tables. Then I would like to print the top 25 values for "site" ranked by total "votes_up".

How can I do this in PHP?

Thanks in advance,

John

Answer 1

You can do something like this (warning: Extremely poor SQL ahead)

select site, sum(votes_up) votes_up
from (
    select site, votes_up from table_1
    UNION
    select site, votes_up from table_2
    UNION
    ...
    UNION
    select site, votes_up from table_56
) group by site order by sum(votes_up) desc limit 25

But, as Dav asked, does your data have to be like this? There are much more efficient ways of storing this kind of data.

Edit: You just mentioned in a comment that you expect there to be more than 56 tables in the future -- I would look into MySQL limits on how many tables you can UNION before going forward with this kind of SQL.

Answer 2

The UNION part of Ian Clelland answer can be generated using a statement like the following. The table INFORMATION_SCHEMA.COLUMNS has a column TABLE_NAME to get all tables.

select * from information_schema.columns 
where table_schema not like 'informat%'
and column_name like 'VOTES_UP'

Join all inner SELECT with UNION ALL instead of UNION. UNION is doing an implicit DISTINCT (on oracle).

Answer 3

The basic idea would be to iterate over all your tables (using a SQL SHOW TABLES statement or similar) in PHP, then for every table, iterate over the rows (SELECT site,votes_up FROM $table). Then, for every row, check the site against an array that you're building with sites as keys and votes up as values. If the site is already in the array, increment its votes appropriately; otherwise, add it.

Vaguely PHP-like pseudocode:

// Build an empty array for use later
$votes_array = empty_array();

// Get all the tables and iterate over them
$tables = query("SHOW TABLES");
for($table in $tables) {
    $rows = query("SELECT site,votes_up FROM $table");

    // Iterate over the rows in each table
    for($row in $rows) {
          $site = $row['site'];
          $votes = $row['votes_up'];

         // If the site is already in the array, increment votes; otherwise, add it
         if(exists_in_array($site, $votes_array)) {
             $votes_array[$site] += $votes;
         } else {
             insert_into_array($site => $votes);
         }
    }
}

// Get the sites and votes as lists, and print out the top 25
$sorted_sites = array_keys($votes_array);
$sorted_votes = array_values($votes_array);
for($i = 0; $i < 25; $i++) {
    print "Site " . $sorted_sites[$i] . " has " . $sorted_votes[$i] . " votes";
}

Answer 4

Here's a PHP code snip that should get it done. I have not tested it so it might have some typos and stuff, make sure you replace DB_NAME

$result = mysql_query("SHOW TABLES");
$tables = array();
while ($row = mysql_fetch_assoc($result)) {
    $tables[] = '`'.$row["Tables_in_DB_NAME"].'`';

}

$subQuery = "SELECT site, votes_up FROM ".implode(" UNION ALL SELECT site, votes_up FROM ",$tables);
// Create one query that gets the data you need
$sqlStr = "SELECT site, sum(votes_up) sumVotesUp
      FROM (
      ".$subQuery." ) subQuery
        GROUP BY site ORDER BY sum(votes_up) DESC LIMIT 25";
$result = mysql_query($sqlStr);
$arr = array(); 
while ($row = mysql_fetch_assoc($result)) { 
    $arr[] = $row["site"]." - ".$row["sumVotesUp"];
} 
print_r($arr)

Answer 5

"I allow users to add tables to the database." - I hope all your users are benevolent and trustworthy and capable. Do you worry about people dropping or truncating tables, creating incorrect new tables that break your code, or other things like that? What kind of security do you have when users can log right into your database and change the schema?

Here's a tutorial on relational database normalization. Maybe it'll help.

Just in case someone else that comes after you wants to find what this could have looked like, here's a single table that could do what you want:

create database bookfeather;
create user bookfeather identified by 'bookfeather';
grant all on bookfeather.* to 'bookfeather'@'%';

use bookfeather;

create table if not exists book
(
    id int not null auto_increment,
    title varchar(255) not null default '',
    upvotes integer not null default 0,
    downvotes integer not null default 0,
    primary key(id),
    unique(title)
);

You'd vote a title up or down with an UPDATE:

update book set upvotes = upvotes + 1 where id = ?

Adding a new book is as easy as adding another row:

insert into book(title) values('grails in action')

I'd strongly urge that you reconsider.