DB layout:
authorization table - for logins
players table - with name and profile
points tables - for point amounts and counts
records table - holds the points posted from the Points Posting Form for all players.
One player can have many records,play many games, have 3 point_types, and from 3-15 point_id's within the types holding point values and a game count. 2 types of points give no count toward games(0)but counted within their individual grouping
Someone made this script for me to give basic totals for each player and a ranking, but I need it to go further and they are nowhere to be found now. I am stuck and have tried making various changes but don't know enough about grouping/SUM to do this. I have done this sort of report with 4th and 5th generation Report Writer languages for years, but never with php/mysql :-(. I don't know the rules and can't find help to GET the rules, lol. I downloaded PHP Manual and loads of tutorials and no help. Does anyone use PHP for numeric data and spreadsheet type scripts?
The script below works fine for basic totals with no grouping of any kind
function getScores() {
// Return associative arrays of member scores
require_once("dbconnect.php");
// One way to do this:
// SELECT nick, SUM( pointamt ) AS "Total Points" FROM records GROUP BY nick ORDER BY `Total Points` DESC;
$query = "SELECT `nick`, `memid` FROM `players`;";
$result = mysql_query($query);
if (!$result) {
die("<strong>Error:</strong> Couldn't get member list from database: <font color=\"red\">".mysql_error()."</font>");
}
if (mysql_num_rows($result) == 0) {
// No members found in db
return(FALSE);
} else {
// Read players into array
while($row = mysql_fetch_assoc($result)){
// $players[$row['memid']]['nick'] = $row['nick'];
$players['nicks'][$row['memid']] = $row['nick'];
}
// Calculate points for each player
foreach ($players['nicks'] as $memid => $player) {
$query = "SELECT SUM(`pointamt`) AS `total` FROM `records` WHERE `nick` LIKE '$player';";
$result = mysql_query($query);
if (!$result) {
die("<strong>Error:</strong> Error getting points for {$player['nick']} from database: <font color=\"red\">".mysql_error()."</font>");
}
// Set to actual 0 if total points result is NULL.
// $players['points'][$memid] = mysql_result($result,0) ? mysql_result($result,0) : 0 ;
if (mysql_result($result,0)) {
$players['points'][$memid] = mysql_result($result,0);
} else {
$players['points'][$memid] = 0;
}
}
// Put points in descending order
arsort($players['points']);
// Give each player a placing/ranking
$i = 1;
foreach ($players['points'] as $memid => $points) {
// Use data function to creat a suffix for number and store both (e.g. 1st, 2nd, 3rd)
$players['ranking'][$memid] = $i . date("S",strtotime("Jan ".$i." 2004"));
$i++;
}
return($players);
}
}
