SUM across multiple tables

[siobhan]

Okay, I am fairly sure that I have gone about this in the absolutely wrong most convoluted way, but... I need to create a list of the top five players for a game (username and score).

There are four tables which house the scores for four different games (game1, game2, etc) and in each table are multiple users with scores for each time they're tried to play the game.

I know how to SUM the user's scores in one table, no problem. I have no idea how to SUM all the player scores across all four tables then pull out the top 5. I've got some code (below) but it is doing strange things because, well, I've written strange statements.

Can anyone help? I am beyond the point of frustration.

Thanks

<?php require_once('Connections/user_auth.php'); ?>
<?php
session_start();

mysql_select_db($database_user_auth, $user_auth);
$query_rshighscore = "SELECT tbl_game1.username, tbl_game2.username, tbl_game3.username, tbl_game4.username, SUM(tbl_game1.userscore), SUM(tbl_game2.userscore), SUM(tbl_game3.userscore), SUM(tbl_game4.userscore) AS highscore FROM tbl_game1, tbl_game2, tbl_game3, tbl_game4 GROUP BY tbl_game1.username, tbl_game2.username, tbl_game3.username, tbl_game4.username ORDER BY highscore ASC LIMIT 5";

$rshighscore = mysql_query($query_rshighscore, $user_auth) or die(mysql_error());

$row_rshighscore = mysql_fetch_assoc($rshighscore);

$totalRows_rshighscore = mysql_num_rows($rshighscore);
?>

<?php do { ?>

<?php echo $row_rshighscore['username']; ?>

<?php echo $row_rshighscore['SUM(tbl_game1.userscore)'];

<?php } while ($row_rshighscore = mysql_fetch_assoc($rshighscore)); ?>

[bastien]

Table design could be improved. Better to have another column in the games_table as GAME_ID which then combines the four tables into 1.

Then its very easy to do this calc.

but in any event there are some other options here.

1. use code to create a running total of the users scores

2. try changing the query to:

$query_rshighscore = "
SELECT tbl_game1.username as username, 
(SUM(tbl_game1.userscore)+ SUM(tbl_game2.userscore)+ SUM(tbl_game3.userscore)+ SUM(tbl_game4.userscore)) AS highscore FROM tbl_game1, tbl_game2, tbl_game3, tbl_game4 
WHERE 
tbl_game1.username=tbl_game2.username AND tbl_game1.username=tbl_game3.username AND 
tbl_game1.username=tbl_game4.username
GROUP BY tbl_game1.username, tbl_game2.username, tbl_game3.username, tbl_game4.username 
ORDER BY highscore ASC LIMIT 5"; 

No guarantees with that sql statement though. my instinct is to create a summary table as outlined at the top.

1. copy the tbl_game1
2. add the game_id column
3. append the other three game tables to the new table, setting the game_id each time

Then the query is much simpler

$query_rshighscore = "
SELECT username, SUM(userscore) AS highscore 
FROM tbl_games
GROUP BY username
ORDER BY highscore ASC LIMIT 5"; 

[siobhan]

Thanks! You are always so helpful. I actually already have a game summary table and the streamlined code you suggested but I had been putting in the totals for each game manually, which got me started on the crazy sql because I realised that was going to be a huge amount of work! Obviously I went about that wrong too!

Just so I understand, when you say...

<snip>
1. copy the tbl_game1
2. add the game_id column
3. append the other three game tables to the new table, setting the game_id each time
</snip>

I've got step one under control. AT step 2 I get slightly confused. Right now in each of the four game tables I already have a primary key game_id column which is specific to the rows in that table. Do I need to add another game_id column? Then at step 3 I'm completely lost. How do I append the other three game tables and how do I set the game_id?

Thanks for all of your help.

[bastien]

what are you using to admin the db? PHPmyAdmin?

if not then use the ALTER TABLE tablename ADD Column (name, size, default value etc)...

The table structure should look something like this:

id (PK autoincrement [big]int)
user_id
game_id (allow nulls)
score
date_played
[other field you need]

to append the other tables

add the game table data
insert into main_table (select user_id, score, date_played from tbl_gameX)

repeat for other tables

add the game id for that game table
update main_table set game_id = X where game_id is NULL

[siobhan]

Thanks for the explanation! Yes, I am using phpmyadmin and this seems quite straightforward. (Although I am still muddling through the functionality of phpmyadmin.)

The only thing I am still confused about is if I change the score in one of the games tables (i.e. they are continually being updated and the highscore will change daily) how does this get reflected in the main_table? I'm assuming the append is a one-off thing, or do I have this wrong?

Sorry for the confusion. You've been very helpful, I just want to be sure I understand!

siobhan

[bastien]

when the conversion is complete and you have tested it for completeness and accuracy built any sql statements needed to preform the queries you need, like this one at the start of this topic, then change the other pages that update the various tbl_games tables so that the sql runs against the new table....

You will have to run the conversion process again to bring everything up to date and then switch every one over to the new scripts.

[siobhan]

Thanks for your help-- this worked well!

I've also tried out the code to see if I could get things to work that way. It sort of works but strange this happen and I'm wondering if you might be able to suggest a reason.

The code returns one player with a score that has a total that doesn't make sense (it doesn't seem to have added the scores in any normal way for instance it returns 145040 when the possible scores for the user in each game table are 12000, 2000, 202, 250).

If I take off some of the tables (if I keep tbl_game1 and tbl_game 2 for instance), it returns 3 users (I only have 3 users in the tables right now for testing). 2 of the users have the correct score, but the third user has an inflated score (34000 instead of
14452). The other two users scores in the tables are (user one: 1500, 3500, 1000, 0 and user two: 777, 0, 1000, 250). So the numbers that are coming out for the third user don't make any sense.

Any idea as to what is happening?

Thanks
sio

[siobhan]

Okay, I've discovered one other thing. I didn't have each of the three users in all of the tables (users might not play all the games for instance). I just made sure that all three users were in each table and the query (the original one with all four tbl_games in it) returned three users two with the correct score and the third with the weird, inflated score.

[bastien]

can you dump me the data for the three users and the sql you are using...then I can test / analyse at home...

[siobhan]

okay. have sent to you via pm--hope that is okay

sio