Menu
As my info states, I inherited the job of statistician for a local dart league. (Woohoo $20 a season)
I would love to implement an ELO rating system.
The current database table has over 100,000 entries.
There are 3 different games that are played. Singles, Doubles, and Team.
The originator of the database has the games entered as such:
Index Player1_num Player2_num Opp_Player1_num Odd_Player2_num H_team V_team Season Week W L Game_type
A singles game is entered twice.
values(1,20,null,30,null,200,300,11,2,1,0,301_singles)
and
values(2,30,null,20,null,200,300,11,2,0,1,301_singles)
It was done this way to facilitate easier look-ups of individuals.
A doubles game would have values such as
(3,20,21,30,31,200,300,11,2,1,0,501_doubles)
(4,21,20,30,31,200,300,11,2,1,0,501_doubles)
(5,30,31,20,21,200,300,11,2,0,1,501_doubles)
(6,31,30,20,21,200,300,11,2,0,1,501_doubles)
There again, it is built for easier queries for looking up wins and losses across seasons and with different partners.
A team game is:
(7,null,null,null,null,200,300,11,2,1,0,team_game)
So I added a match_num column to the table to help lump the games under 1 number.
However, I am unsure how best to assign the numbers, understandably, I don't feel like going through all 90k entries by hand.
Note: The early seasons were modified by hand and not all double matches have 4 entries (some only have 1). Some matches may be out of order as well (Instead of indexes: 1,2 they could be 1,9).
I don't know if you need more info for this, and I am unsure of how to post larger examples of the table.
Current code:
<body>
<?php
include "inc.php";
// Select Max Match Number
$sqlMatch="select max(match_num) from stats_results";
$match =mysql_query($sqlMatch);
$m= $match ? mysql_result($match,0):mysql_error();
$matchno= $m+1; // First Match number
$game=array("'301_singles'","'cricket_singles'","'501_singles'","'301_doubles'","'cricket_doubles'");
// selects max season
$sqlSeason="select max(season_index) from stats_season";
$season =mysql_query($sqlSeason);
$smax= $season ? mysql_result($season,0):mysql_error();
# $smax=2;
// for each season up to max season
for($s=1;$s<=$smax;$s++)
{
// Selects max week within the current season
$sqlWeek="select max(week) from stats_results where season=$s ";
$Week =mysql_query($sqlWeek);
$wmax= $Week ? mysql_result($Week,0):mysql_error();
# $wmax=2;
// for each week within current season up to the max week
for ($w=1;$w<=$wmax;$w++)
{
// each singles game
foreach($game as $g)
{
###########################################################################################################
$result = mysql_query("SELECT * FROM stats_results where season=$s and week=$w and game_code=$g ;") or die(mysql_error());
// Put them in array
for($i = 0; $rows[$i] = mysql_fetch_assoc($result); $i++) ;
// Delete last empty one
array_pop($rows);
//******************************************************
$matches=array();
foreach($rows as $record)
{
// Get a unique match code for this match
$matchid= getMatchID($record);
// Have we seen this ID before? If yes add this record index to the existing array
// otherwise create an array with just this value
if (!isset($matches[$matchid])) $matches[$matchid]= array($record['index_results']); // No
else $matches[$matchid][]= $record['index_results']; // Yes, add this Index
}
// Now $matches is an array of arrays of Index values, grouped by "match" so...
/* Sort $matches by key (see getMatchID for why!) */
ksort($matches);
// Update the table
foreach($matches as $match)
{
// Create SQL instruction to set the match_num for all the Index values in each entry
$sql= "UPDATE stats_results SET match_num = $matchno WHERE index_results IN (".implode(",", $match).")";
echo "<br>";
echo $sql;
/* Execute the SQL using your chosen method! */
// Move the match count on
$matchno++;
}
// End our loops
}
}
}
function getMatchID($gamerecord)
{
$index= "{$gamerecord['season']}-{$gamerecord['week']}-{$gamerecord['game_code']}-";
$players= array(
$gamerecord['player1_num'],
empty($gamerecord['player2_num'])?0:$gamerecord['player2_num'],
$gamerecord['opp_player1_num'],
empty($gamerecord['opp_player2_num'])?0:$gamerecord['opp_player2_num']
);
// Sort the players to get them in a consistent order
sort($players);
// Add the sorted players to the index
$index.= implode('-', $players);
return $index;
}
?>
</body>
867
(I'm putting this into an Answer so I can get it to layout a bit better - but it's not really a "solution" as such!)
I think what I'd do is try and build an array of arrays of Index values grouped by game - you can then use that to create SQL to update the table.
So, if $rows is an array of all records we'd do something like the following pseudo-code:
$matches= array();
foreach($rows as $record)
{
// Get a unique match code for this match
$matchid= getMatchID($record);
// Have we seen this ID before? If yes add this record index to the existing array
// otherwise create an array with just this value
if (!isset($matches[$matchid])) $matches[$matchid]= array($record['Index']); // No
else $matches[$matchid][]= $record['Index']; // Yes, add this Index
}
// Now $matches is an array of arrays of Index values, grouped by "match" so...
/* Sort $matches by key (see getMatchID for why!) */
ksort($matches);
// Update the table
$matchno= 1; // First Match number
foreach($matches as $match)
{
// Create SQL instruction to set the match_num for all the Index values in each entry
$sql= "UPDATE games SET match_num = $matchno WHERE Index IN (".implode(",", $match).")";
/* Execute the SQL using your chosen method! */
// Move the match count on
$matchno++;
}
So all that leaves is the getMatchID function - if we give each match a temporary ID based on it's season, week and a sorted list of it's participants (and use the Season and Week first) that should be unique for each game and we can sort by this index later to get the games in the right order. So again in rough pseudo-code, something like:
function getMatchID($gamerecord)
{
$index= "{$gamerecord['Season']}-{$gamerecord['Week']}-{$gamerecord['H_Team']}-{$gamerecord['V_Team']}-";
$players= array(
empty($gamerecord['Player1_Num'])?0:$gamerecord['Player1_Num'],
empty($gamerecord['Player2_Num'])?0:$gamerecord['Player2_Num'],
empty($gamerecord['Opp_Player1_Num'])?0:$gamerecord['Opp_Player1_Num'],
empty($gamerecord['Opp_Player2_Num'])?0:$gamerecord['Opp_Player2_Num']
);
// Sort the players to get them in a consistent order
sort($players);
// Add the sorted players to the index
$index.= implode('-', $players);
return $index;
}
So all being well $index would come back with something like 11-2-200-300-0-0-20-30 for the first singles match in your example - no matter which of the game records we were looking at.
Does that make sense/help at all?
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
