Golf Handicap Index Calculator HELP

[wchamber22]

Hello Freaks,

 

Sorry this will be a long-winded explaination, be prepared.

I am hole-heartedly trying to create a golf index calculator. It works like this:

 

A user must have a min of 5 rounds in order to have an index.  The calculator will compute no more than a users last 20 rounds.

Below is a chart of how it is calculated:

 

Number of differentials used

Rounds Entered                                  Diferentials Used

5-6                                                    Use the lowest differential

7-8                                                    Use the 2 lowest differentials

9-10                                                  Use the 3 lowest differentials

11-12                                                Use the 4 lowest

13-14                                                Use the 5 lowest

15-16                                                Use the 6 lowest

17                                                      Use the 7 lowest

18                                                      Use the 8 lowest

19                                                      Use the 9 lowest

20 or more                                          Use the 10 lowest (of the 20 most recent rounds)

 

For instance, if I have entered 12 rounds, my index would be calculated by taking the 4 lowest differentials of those 12 rounds, adding those 4 differentials together and dividing by 4.

Last example: If I have 32 rounds entered, my index would be calculated by taking the 10 lowest differentials FROM THE MOST RECENT 20 ROUNDS, adding those 10 differentials together and dividing by 10.

 

My db table SCORES looks like this:

scoreID

userID (id of user that entered score)

courseID (id of the course the round was played)

date (date of the round)

tee (what tee the user played from)

score (users score)

differential: Type:DECIMAL(3,2) (round differential - I was able to calculate this before inserting into the db)

 

So far I haven't much doing, see below:

$sql3 = mysql_query("SELECT differential FROM scores WHERE userID='$user_id' ORDER BY date DESC");
$score_count = mysql_num_rows($sql3);
if($score_count <= 4){
   $scoreMsg = 'You have ' . $score_count . ' of 5 rounds entered to complete your handicap index.';
}elseif($score_count == 5 || $score_count == 6){
   $rsDiffs = mysql_fetch_array($sql3);
   $userIndex = min($rsDiffs);
   $scoreMsg = 'You have ' . $score_count . ' rounds entered<br/><br/>
                       Your HANDICAP INDEX is now: ' . $userIndex .'';
   //This one I THINK I can use the MIN function because it uses the single lowest differential of the $rsDiffs array, WILL THIS WORK?
}elseif($score_count == 7 || $score_count == {
   $rsDiffs = mysql_fetch_array($sql3);
   //$userIndex = This one will use the 2 lowest diffentials of the $rsDiffs array, SO HOW DO I NARROW THE $rsDiffs array TO THE LOWEST 2, ADD THE 2 TOGETHER AND DIVIDE BY 2?
   $scoreMsg = 'You have ' . $score_count . ' rounds entered<br/><br/>
                       Your HANDICAP INDEX is now: ' . $userIndex .'';
}elseif($score_count == 9 || $score_count == 10){
   //Same as above BUT this one will use the 3 lowest of the 9 or 10 entered scores.
}elseif($score_count == 11 || $score_count == 12){
   //Same as above BUT this one will use the 4 lowest of the 11 or 12 entered scores.
}elseif($score_count == 13 || $score_count == 14){
   //Same as above BUT this one will use the 5 lowest of the 13 or 14 entered scores.
}elseif($score_count == 15 || $score_count == 16){
   //Same as above BUT this one will use the 6 lowest of the 15 or 16 entered scores.
}elseif($score_count == 17){
   //Same as above BUT this one will use the 7 lowest of the 17 entered scores.
}elseif($score_count == 18){
   //Same as above BUT this one will use the 8 lowest of the 18 entered scores.
}elseif($score_count == 19){
   //Same as above BUT this one will use the 9 lowest of the 19 entered scores.
}elseif($score_count >= 20){
   //This is where it really gets complicated IN MY MIND, this one will use the 10 lowest of 20 scores entered OR the 10 lowest of the 20 most recent rounds if $scores_count > 20.
}

 

Once again, sorry for the long-winded request!  I think if I get some guidance on the first if(<=4), a middle if-else(==5 || ==6) and the final if-else(>=20) I will be able to figure out the rest.

 

Any help would be much appreciated!

 

Thanks Freaks.

[requinix]

1. Put a LIMIT 20 on that query. No sense pulling more results than you could possibly use.

2. If the user doesn't have 5 rounds yet, stop.

3. Load up all the scores into an array and sort it.

4a. Use the control structure of your choice to get a subset of the array.

4b. Protip: for 5-16 rounds you can floor(($rounds - 3) / 2), with 17-19 $rounds - 10, otherwise 20. Try the math yourself if you're not sure of it.

5. Since PHP doesn't have a built-in "average" function, figure it out yourself.

 

That will result in a fairly short chunk of code, and the confusing logic doesn't even play a part in most of it.

[Psycho]

Although I believe I understand what you are trying to achieve, I'm not really understanding what your exact question is. But, based upon what I understand, this is what I would do.

 

//Get the users total rounds
$query = "SELECT COUNT(scoreID)
          FROM scores
          WHERE userID='$user_id'";
$result = mysql_query($query);
$total_rounds = mysql_result($result, 0);

if($total_rounds<5)
{
    $scoreMsg = "You have {$total_rounds} of 5 rounds entered to complete your handicap index.";
}
else
{
    //Determine how many scores will be used
    if($total_rounds<7) { $score_count = 1; }
    elseif($total_rounds<9) { $score_count = 2; }
    elseif($total_rounds<11) { $score_count = 3; }
    elseif($total_rounds<13) { $score_count = 4; }
    elseif($total_rounds<15) { $score_count = 5; }
    elseif($total_rounds<17) { $score_count = 6; }
    elseif($total_rounds<18) { $score_count = 7; }
    elseif($total_rounds<19) { $score_count = 8; }
    elseif($total_rounds<20) { $score_count = 9; }
    else { $total_rounds = 10; }
    
    //Get the differenential from (up to) the last 20 rounds
    $query = "SELECT differential
              FROM scores
              WHERE userID='$user_id'
              ORDER BY date DESC
              LIMIT 20";
    $result = mysql_query($query);

    //Put differential scores into array
    $score_results = array();
    while($row = mysql_fetch_assoc($result))
    {
        $score_results[] = $row['differential'];
    }

    //Sort the scores, lowest to highest
    sort($score_results);
    
    //Get the slice of the array with only the scores we want
    $score_results = array_slice($score_results, 0, $score_count);
    //Calculate the sum of the scored
    $handicap = array_sum($score_results) / $score_count;
    $scoreMsg  = "You have {$total_rounds} rounds entered<br/><br/>\n";
    $scoreMsg .= "Your HANDICAP INDEX is now: {$handicap}";
}

[Psycho]

1. Put a LIMIT 20 on that query. No sense pulling more results than you could possibly use.

 

I had initially stated the same thing in my reply/code, but then changed it. In the output to the user he wants to display the total rounds back to the user. But, for the purposes of the calculations only the last 20 are needed.

[ignace]

else { $total_rounds = 10; }

 

Shouldn't this be $score_count = 10;

[Psycho]

else { $total_rounds = 10; }

 

Shouldn't this be $score_count = 10;

 

Yep, I changed some variable names halfway through when I realized what they really contained. Missed updating that one. Thx.

[wchamber22]

Hello psycho and ignace,

 

Thanks for your help.  This code makes complete sense. I learned alot and think I can reuse some pricipals learned from this code in other areas of the site.

 

FYI, psycho: you totally understood what I was trying to do.

 

I am sure I will be back before I finish this site, Later.

[xyph]

3. Load up all the scores into an array and sort it.

 

Why not sort in the query?

 

SELECT differential FROM scores WHERE userid = $uid ORDER BY date DESC, differentials ASC LIMIT 20

 

Then you can simply mysql_num_rows to find how many values you want to actually grab.

 

for( $i = 0; $row = mysql_fetch_assoc($result) && $i < $lowest_x; $i++ ) {...

 

1. Put a LIMIT 20 on that query. No sense pulling more results than you could possibly use.

 

I had initially stated the same thing in my reply/code, but then changed it. In the output to the user he wants to display the total rounds back to the user. But, for the purposes of the calculations only the last 20 are needed.

 

I'd say performing a second query, or grabbing COUNT(*) as total in each row above (not sure which one would be faster off-hand, I'd guess a second query) would be better than grabbing a bunch of data you aren't going to use. Then again, if you're only selecting small integers, it might not be a big deal.

[Psycho]

Why not sort in the query?

 

SELECT differential FROM scores WHERE userid = $uid ORDER BY date DESC, differentials ASC LIMIT 20

 

Then you can simply mysql_num_rows to find how many values you want to actually grab.

 

That won't work. He needs the three lowest differentials from the last 20 rounds. Using the above approach you might end up with the following results

DATE    | Diff_Score

8-10-12  12

8-09-12  24

8-08-12  8

8-07-12  15

8-06-12  7

8-05-12  18

8-04-12  12

8-03-12  5

8-02-12  22

 

If the user only had 10 rounds with the above differential scores the lowest scores would not be at the top of the result set. I actually tried doing a query on the lowest differentials using a sub-query of the last 20 rounds. But, I couldn't get it to work. The above approach works. It may not be the most efficient, but it would work. I don't think it would add an significant performance issues - if any at all. But, I would like to see a query only solution if someone has one.

 

[xyph]

Oh ya, ooooops! Talk about not thinking it through.

 

Sorry about that. Colour coded and everything!