Page 1 of 1

Select Players While Accruing Points

Posted: Wed Nov 09, 2011 9:43 am
by JD-AM
Hi Everyone,

I'm developing a website for a local Poker tournament. The tournament has many locations that members can go to, with leaderboards for Single Game, Location, State & Major.

Each game is inputed by the client by CSV, broken up and put into the database, updating members details, followed by inserting all game specific details into a games table which looks like this:

ID, TIMESTAMP, LOCATION ID, PLAYER ID, POINTS

What I am trying to do is, when viewing the leaderboard for a particular location, the mySQL Select statement needs to somehow accrue points of players, before displaying the final leaderboard in descending order.

This is what I have at the moment, without consideration for adding points of each player:

Code: Select all

SELECT * FROM games WHERE locationID = $theLocation ORDER BY points DESC
I just need to know if there is a way to add up points on multiple rows that have a matching LOCATION ID & PLAYER ID, as well as a gereral count of TIMES PLAYED to be computed before outputting results.


Cheers.

Re: Select Players While Accruing Points

Posted: Wed Nov 09, 2011 12:11 pm
by mikeashfield
I think the two queries you're looking for are:

Code: Select all

SELECT *, COUNT(playerID) AS playerID_count FROM games WHERE playerID = $playerID; //counts the number of times playerID occurs.
SELECT * FROM games where locationID = $theLocation AND playerID = $playerID ORDER BY  points DESC; //gets all records where location and playerID match.
These may be slightly wrong, as I'm still quite new to PHP anad MySQL myself, but they should be enough to point you in the right direction. :mrgreen:

Re: Select Players While Accruing Points

Posted: Thu Nov 10, 2011 12:42 am
by JD-AM
Thanks for your reply.

Its not exactly what I'm after though. While I do need the count of how many times a player has played at the venue, the more pressing issue is figuring out how to add all values from the points field for a player, while at the same time, displaying a leaderboard from that location with ALL players, each with there own totalled score. The database, for aguments sake, will look like this:

Code: Select all

ID       Timestamp        LocationID           PlayerID           Points
----------------------------------------------------------------------------------
1        124356              00123                  472                  12
2        189655              00123                  472                  30
3        208485              00123                  472                  100
4        228505              00123                  472                  0

And the display will look like this:

Code: Select all

Pos     PlayerID           Points            TImes Played
--------------------------------------------------------
1        472                 142                4
2        899                 102                2
3        296                 85                 3
Notice, player 472's points are added up values of his points for each game at the location.


Thanks.

Re: Select Players While Accruing Points

Posted: Thu Nov 10, 2011 2:20 am
by mikeashfield
Ok, I'm with ya, then in that case the SQL statement for counting times plays looks good.
As for the total points, this may be of interest: http://php.net/manual/en/function.array-sum.php

Like this:

Code: Select all

<?php
//For each row returned from the SQL statement for player & location loop the following.
while($row = mysql_fetch_array($result)){
//Define the variable playerTotal to store their total score, and for each row, sum all results.
$playerTotal = $row['SUM(points)'];
}

//Display the result
echo $playerTotal;
?>

Hope this can help :D