“Necessity is the mother of invention”…
It’s no secret that I love golf—honestly, that’s certainly an understatement. The best part is, I’m far from alone. For the past twenty years, give or take a few weekends and the occasional weather delay, I’ve had a group of friends who meet up every Sunday to play wherever we can get a tee time. In the spirit of friendly competition, we’ve turned it into a weekly skins game for points, bragging rights, and now even a trophy called the Chariot Cup .
Since things have gotten a bit more “serious,” I started tracking scores, stats, and—most importantly— points throughout the year. That way, we can properly crown the winner with the trophy and the all-bragging rights that go along with it, so this is what I came up with.
The website is built in PHP with a MySQL backend. It tracks users’ handicaps using the same calculation method as GHIN. Suffice to say, it’s far more complex than a simple 1+1=2 equation. Here is a link to how the USGA does it.
Since I’m a bit of a nerd, and know SQL, PHP, JavaScript and host a website it just seemed like a good fit… I used Google’s visualizations for the dash-boarding..
Handicaps are based on differentials, a score differential is calculated for each round played and is different for each course, based on the slope/course rating of the tees played. We then count the differentials that have been recorded, if less than 20 then a certain number of records will be used. If over 20, then use the lowest 8 and use those to calculate the handicap index.
The SQL becomes interesting based on the rounds played…
// Build the Query
$sql = "insert into scores (date, user_id, course_id, league_id, tee_id, front_score, back_score, total_score, points, differential) VALUES ('{$x_date}', {$x_userid}, {$x_courseid}, {$x_leagueid}, {$x_teeid}, {$x_frontscore}, {$x_backscore}, {$x_totalscore}, {$x_points}, {$sqlDifferential});";
$sumRow = array($x_frontscore,$x_backscore,$x_totalscore); //create array of data to "Sum" the numbers for validating
********************************************************************************************************
//1) pull the count of differentials and set a variable to the number
$sqlQuery = mysqli_query($conn, "SELECT count(*) differential_count FROM scores WHERE differential IS NOT NULL AND user_id = $_userid;");
$countRow = mysqli_num_rows($sqlQuery);
if($countrow = 1){
$rowData = mysqli_fetch_array($sqlQuery);
$diffCount = $rowData[0];
} else {
$diffCount = 0;
}
******************************************************************************************************** //2) use that count in the switch function to build and execute the correct sql statement for calculating the HC
switch (true) {
case ($diffCount < 3): //the user doesn't have enough scores for a handicap... so avg. the scores.
$sqlHC = mysqli_query($conn, "SELECT user_id, sysdate() date, ROUND(AVG(CASE WHEN total_score > 0 THEN total_score ELSE (front_Score + back_score) END)) -72 handicap FROM scores WHERE user_id = $_userid AND differential IS NOT NULL;");
break;
case ($diffCount == 3) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3)-2 handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 1) t1;");
break;
case ($diffCount == 4) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3)-1 handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 1) t1;");
break;
case ($diffCount == 5) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3) handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 1) t1;");
break;
case ($diffCount == 6) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3)-1 handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 2) t1;");
break;
case ($diffCount == 7 || $diffCount == 8) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3) handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 2) t1;");
break;
case ($diffCount == 9 || $diffCount == 10 || $diffCount == 11) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3) handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 3) t1;");
break;
case ($diffCount == 12 || $diffCount == 13 || $diffCount == 14) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3) handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 4) t1;");
break;
case ($diffCount == 15 || $diffCount == 16) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3) handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 5) t1;");
break;
case ($diffCount == 17 || $diffCount == 18) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3) handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 6) t1;");
break;
case ($diffCount == 19) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3) handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 7) t1;");
break;
case ($diffCount > 19) :
$sqlHC = mysqli_query($conn, "SELECT t1.user_id, SYSDATE()date, LEFT(AVG(t1.differential) ,3) handicap FROM (SELECT user_id, differential FROM scores WHERE differential IS NOT NULL AND differential <> 0 AND user_id = $_userid ORDER BY differential ASC LIMIT 8) t1;");
break;
}
********************************************************************************************************
//3) get the record from the handicap calcuation, build the sql for saving that data by WRITE to the HC table
$HCData = mysqli_fetch_array($sqlHC);
$sql = "INSERT INTO handicap (user_id, date, hc) VALUES ({$HCData[0]}, '{$HCData[1]}', ROUND({$HCData[2]},1));";
// execute mysql query for each record
$sqlQuery = mysqli_query($conn, $sql);
//Atleast 1 record has been entered
$record_entered = "true";
if(!$sqlQuery){
die("MySQL HC entry failed!" . mysqli_error($conn));
}
}
}
********************************************************************************************************The 18 and 9 hole scores are calculated slightly differently, where two 9 holes used to have to be added together before it was included in the handicap. GHIN has since updated how they calculate the 9 hole score, but this should give you an idea of how it works.
** 18 Hole Scores **
SELECT s.RECORD_DTE_TME, s.user_id, ROUND((((s.total_score - t.rating)*113)/t.slope),1) differential
FROM scores s, league l, tees t
WHERE s.LEAGUE_ID = l.LEAGUE_ID
AND s.TEE_ID = t.TEE_ID
AND s.user_id = 50
AND total_Score > 0
UNION
SELECT s.RECORD_DTE_TME, s.user_id, ROUND(((((s.front_score + s.back_score) - t.rating)*113)/t.slope),1) differential
FROM scores s, league l, tees t
WHERE s.LEAGUE_ID = l.LEAGUE_ID
AND s.TEE_ID = t.TEE_ID
AND s.user_id = 50
AND s.front_Score > 0
AND s.back_score > 0
UNION
SELECT s.RECORD_DTE_TME, s.user_id, ROUND(((((s.front_score) - (t.rating/2))*113)/t.slope),1) differential
FROM scores s, league l, tees t
WHERE s.LEAGUE_ID = l.LEAGUE_ID
AND s.TEE_ID = t.TEE_ID
AND s.user_id = 50
AND s.front_score > 0
AND s.back_score = 0
UNION
SELECT s.RECORD_DTE_TME, s.user_id, ROUND(((((s.back_score) - (t.rating/2))*113)/t.slope),1) differential
FROM scores s, league l, tees t
WHERE s.LEAGUE_ID = l.LEAGUE_ID
AND s.TEE_ID = t.TEE_ID
AND s.user_id = 50
AND s.back_score > 0
AND s.front_score = 0For some of the visuals, we need AVG., MIN and MAX scores…
//******************************************************************************************************
// *** Get data needed for ALL TIME LOW GUAGE and build data needed since a league has been selected*********
//******************************************************************************************************
$pieResult = mysqli_query($conn, "SELECT sc.user_id, ROUND((sum(sc.total_score)/sum(sc.count)),1) avg_score FROM (SELECT s.user_id, count(*) count, sum(s.total_score) total_score FROM scores s, league l WHERE l.league_id = s.league_id AND s.user_id = $user_id AND total_Score > 0 AND l.league_name = '{$_btnLeague}' UNION SELECT s.user_id, count(*) count, sum(s.front_score + s.back_score) total_score FROM scores s, league l WHERE l.league_id = s.league_id AND s.user_id = $user_id AND s.front_Score > 0 AND s.back_score > 0 AND l.league_name = '{$_btnLeague}') SC GROUP BY user_id;");
//variables needed
$_AllTimeAvgData = $scores = '';
if(mysqli_num_rows($pieResult) > 0) { //Are there records to display???
//**** GET DATA ****
while($row = mysqli_fetch_array($pieResult)){
$scores.= "['Avg Score'," . $row['avg_score'] . "]" . PHP_EOL;
}
$_AllTimeAvgData = "['Label','Value'],". $scores . PHP_EOL;
} else {
$_AllTimeAvgData = "['Label','Value']," . "[n/a,1]";
}
//***********************************************************************************************
**YTD AVG SCORE**
SELECT sc.user_id, ROUND((sum(sc.total_score)/sum(sc.count)),1) avg_score
FROM (
SELECT s.user_id, count(*) count, sum(s.total_score) total_score
FROM scores s, league l, (SELECT MAX(YEAR(date)) MAX_DATE from scores) md
WHERE l.league_id = s.league_id
AND year(s.date) = (md.MAX_DATE)
AND s.user_id = $user_id
AND total_Score > 0
AND l.league_name = '{$_btnLeague}'
UNION
SELECT s.user_id, count(*) count, sum(s.front_score + s.back_score) total_score
FROM scores s, league l, (SELECT MAX(YEAR(date)) MAX_DATE from scores) md
WHERE l.league_id = s.league_id
AND year(s.date) = (md.MAX_DATE)
AND s.user_id = $user_id
AND s.front_Score > 0
AND s.back_score > 0
AND l.league_name = '{$_btnLeague}') SC
GROUP BY user_id
**YTD LOW SCORE**
SELECT sc.user_id, MIN(sc.total_score) low_score
FROM(
SELECT s.user_id, s.total_score total_score
FROM scores s, league l, (SELECT MAX(YEAR(date)) MAX_DATE from scores) md
WHERE l.league_id = s.league_id
AND year(s.date) = (md.MAX_DATE)
AND s.user_id = $user_id
AND total_Score > 0
AND l.league_name = '{$_btnLeague}'
UNION
SELECT s.user_id, MIN(s.front_score + s.back_score) total_score
FROM scores s, league l, (SELECT MAX(YEAR(date)) MAX_DATE from scores) md
WHERE l.league_id = s.league_id
AND year(s.date) = (md.MAX_DATE)
AND s.user_id = $user_id
AND s.front_Score > 0
AND s.back_score > 0
AND l.league_name = '{$_btnLeague}')sc
**ALL TIME AVG**
SELECT sc.user_id, ROUND((sum(sc.total_score)/sum(sc.count)),1) avg_score
FROM (
SELECT s.user_id, count(*) count, sum(s.total_score) total_score
FROM scores s, league l
WHERE l.league_id = s.league_id
AND s.user_id = $user_id
AND total_Score > 0
AND l.league_name = '{$_btnLeague}'
UNION
SELECT s.user_id, count(*) count, sum(s.front_score + s.back_score) total_score
FROM scores s, league l
WHERE l.league_id = s.league_id
AND s.user_id = $user_id
AND s.front_Score > 0
AND s.back_score > 0
AND l.league_name = '{$_btnLeague}') SC
GROUP BY user_id
**ALL TIME LOW SCORE **
SELECT sc.user_id, MIN(sc.total_score) low_score
FROM(
SELECT s.user_id, s.total_score total_score
FROM scores s, league l
WHERE s.user_id = $user_id
AND total_Score > 0
AND l.league_name = '{$_btnLeague}'
UNION
SELECT s.user_id, MIN(s.front_score + s.back_score) total_score
FROM scores s, league l
WHERE s.user_id = $user_id
AND s.front_Score > 0
AND s.back_score > 0
AND l.league_name = '{$_btnLeague}')scHave fun!
