tags:

views:

135

answers:

5

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

+1  A: 

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.

Ian Clelland
I allow users to add tables to the database. Is there a way I could make the code allow unions up until the last table, regardless of how many there are in the database?
It's not for a union, but the numbers are likely to be similar... "The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. This also applies to LEFT and RIGHT OUTER JOINS." For MySQL 5.0
Amber
Another source says "There is a limit of "256 tables per SELECT statement". You can use more than 256 tables in a query with UNION statements, but such a query cannot be used as a view or as a subquery for a SELECT statement. If you use such a query directly, you may hit a stack space limit of the query optimizer at around 1300-1500 SELECT-s."
Amber
A: 

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).

Christian13467
A: 

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";
}
Tim
A: 

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)

Saggi Malachi
I tried printing out $result in a table but I got an error message. Do you know what code I should use? Thanks
you should run over it using something like:$arr = array();while ($row = mysql_fetch_assoc($result)) { $arr[] = array("site"=> $row["site"],"sumVotesUp" => $row["sumVotesUp"]);}print_r($arr);for this to work you should add an alias to the sum(votes_up) in the query that goes into $sqlStr. I'll fix it in my answer.
Saggi Malachi
I also added this snip to the answer's code so you could see it with proper formatting
Saggi Malachi
I'm getting an error message for the line with "while ($row = mysql_fetch_assoc($result)) ". Up above, I'm using "$tables[] = $row1["Tables_in_bookfeather"];"... I'm not sure if this is right. Is it?
if that's your database name then it should be good. you get it in the first while(...mysql_fetch_assoc...) or the second one?try printing out mysql_error() for getting more hints
Saggi Malachi
the error message is for the second one
can you please print mysql_error() right after the $result=mysql_query($sqlStr); line?
Saggi Malachi
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ever UNION ALL SELECT site, votes_up FROM biography UNION ALL SELECT site, votes'
ok, I think I've fixed it. look at my answer, I've changed the $subQuery = ... line
Saggi Malachi
I'm still getting the exact same error
uhm, very weird. any chance you could print $sqlStr and paste it to pastebin.com?
Saggi Malachi
ok, I just posted it under the name "Arizona John"
what's the url? not very easy to find stuff there
Saggi Malachi
http://pastebin.com/m4d4aaf3b
oh. you got spaces in your table names :S ok, check my change in the $tables[]=.. line. I've added backticks around the table names so it'd accept spaces.
Saggi Malachi
ok... almost there. Now, it prints the info I want, but each book looks like "Array ( [0] => Array ( [site] => gone with the wind [sumVotesUp] => 63". How could I get it to look like "gone with the wind 63"?
check my edit of the $arr[]=.... line
Saggi Malachi
BTW, you could also just replace it with echo $row["site"]." - ".$row["sumVotesUp"]."\n";
Saggi Malachi
A: 

"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.

duffymo