SQL highscores.

Question

Ok, I have two MYSQL tables:

CREATE TABLE `sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `runTime` int(11) NOT NULL,
  `monstersKilled` int(11) NOT NULL,
  `profit` int(11) NOT NULL,
  `tasks` int(11) NOT NULL,
  `xpGain` int(11) NOT NULL,
  `lastupdate` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

And,

CREATE TABLE  `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `user` text NOT NULL,
  `pass` text NOT NULL,
  `paypal` text NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `strikes` int(11) NOT NULL DEFAULT '0',
  `session` text NOT NULL,
  `brand` text NOT NULL,
  `identifier` text NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=651 DEFAULT CHARSET=latin1;

As you can see, sessions has a link to the user's ID. Now I want to make a total high scores PHP file, but with my little knowledge of php and little knowledge of MYSQL I wouldn't know how to start. The total high scores would be sorted by Total runtime.

Example: In users I have user #1 (cody) & user #2 (Joe). Now, In sessions I have 3 sessions:

id, userID, runTime, monstersKilled, profit, tasks, xpGain, lastupdate
12, 1, 27, 14, 6200, 0, 5050, 1282325410
19, 1, 18, 1, 277, 1, 168, 1278897756
1968, 2, 195, 433, 111345, 4, 73606, 1280993244

The print out should be along the lines of:

Place, username, Total Run Time, Total Monsters Killed, Total profit, Total tasks, Total Exp Gain
1. Joe, 195, 433,11345,4,73606
2. Cody, 55, 15, 1, 5218

Answer 1

Use:

  SELECT u.user,
         SUM(s.runtime) AS total_run_time,
         SUM(s.monsterskilled) AS total_monsters_killed,
         SUM(s.profit) AS total_profit,
         SUM(s.tasks) AS total_tasks, 
         SUM(s.xpgain) AS total_xp_gained
    FROM USERS u
    JOIN SESSION s ON s.userid = u.userid
GROUP BY u.user
ORDER BY total_run_time DESC

Answer 2

This script should output the highscores in a neat table for you. Thanks to OMG Ponies for the SQL query.

<?php
$dbhost = 'localhost';  // Database Host
$dbuser = 'user';       // Database User
$dbpass = 'password';   // Database Password
$dbname = 'database';   // Database Name

$db = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $db);

$limit = 10; // The number of users to show in the top highscores.  Set to 0 to show all

$sql = 'SELECT u.user,
         SUM(s.runtime) AS total_run_time,
         SUM(s.monsterskilled) AS total_monsters_killed,
         SUM(s.profit) AS total_profit,
         SUM(s.tasks) AS total_tasks, 
         SUM(s.xpgain) AS total_xp_gained
        FROM USERS u
        JOIN SESSION s ON s.userid = u.userid
        GROUP BY u.user
        ORDER BY total_run_time DESC';

if ($limit > 0) {
    $sql .= ' LIMIT '.$limit;
}

$get_scores = mysql_query($sql);
$scores = array();

$rank = 0;
while($score = mysql_fetch_array($get_scores)) {
    ++$rank;
    $scores[] = '<td>'.$rank.'</td><td>'.$score['user'].'</td><td>'.$score['total_run_time'].'</td><td>'.$score['total_monsters_killed'].'</td><td>'.$score['total_profit'].'</td><td>'.$score['total_tasks'].'</td><td>'.$score['total_xp_gained'].'</td>';
}

echo '<table><tbody>';
echo '<tr><td>Place</td><td>Username</td><td>Total Run Time</td><td>Total Monsters Killed, Total profit</td><td>Total tasks</td><td>Total Exp Gain</td></tr><tr>';
echo implode('</tr><tr>', $scores);
echo '</tr></tbody></table>';
?>