current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite

UPDATE 2 (Players Handicap Index Calculation)

    $sql3 = "SELECT roundID FROM rounds WHERE userID='$userID'";
    $result3 = mysql_query($sql3) or die(mysql_error());
    $total_rounds = mysql_num_rows($result3);


    //CALCULATE USER HANDICAP INDEX IF TOTAL_ROUNDS > 4
    if($total_rounds > 4){              
        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 { $score_count = 10; }

        $sql2 = "SELECT differential FROM rounds WHERE userID='$userID' ORDER BY date DESC LIMIT 20";
        $result2 = mysql_query($sql2) or die(mysql_error());

        $diff_results = array();
        while($row = mysql_fetch_assoc($result2)){
            $diff_results[] = $row['differential'];
        }

        sort($diff_results);

        $diff_results = array_slice($diff_results, 0, $score_count);

        $handicapIndex = array_sum($diff_results) / $score_count * 0.96;
        $handicapIndex = (floor($handicapIndex * 10)) / 10;

Hopefully this will give you all and idea of how I calculate a players handicap index. Now I would like to show the player (user) the rounds (date ordered) that are used to calculate his index.

Always appreciative!

UPDATE (structure of rounds table)

    roundID - auto incrementing primary key
    userID - INT
    courseID - INT
    tee - VARCHAR
    differential - FLOAT
    date - DATE

I am struggling to even get started with this feature I am trying to implement. Any help would be much appreciated.

I have a set of mysql db results I would like to sort by field differential. I pull them out of the db like this:

    $sql4 = "SELECT * FROM rounds WHERE userID='$userID' ORDER BY date DESC LIMIT 20";
    $result4 = mysql_query($sql4) or die(mysql_error());
    $total_rounds = mysql_num_rows($result4);

As you can see above I counted the rows returned to run through this if-elseif-else statement to figure out how many scores I need to highlight with different css:

    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 { $score_count = 10; }

For example, if $total_rounds = 16 my $score_count would be 6. Now I need to take this data set of 16 rows and spit it out with php so I maintain my ORDER BY date while applying a different css format to the 6 figured in the above if-elseif-else statement. The $score_count is figured because I need to highlight (aka apply different css) to the 6 lowest scores of the 16 row data set WHILE maintaining my date order.

The desired output would look like this (with the * denoting the separate css format *).

01-08-2013 - 16

01-07-2012 - 1 *

01-06-2013 - 15

01-05-2012 - 2 *

01-04-2013 - 14

01-03-2012 - 3 *

01-02-2013 - 13

01-01-2012 - 4 *

12-31-2012 - 12

12-30-2012 - 5 *

12-29-2012 - 11

12-28-2012 - 6 *

12-27-2012 - 10

12-26-2012 - 9

12-25-2012 - 8

12-24-2012 - 7

Please let me know if you have questions.

Thanks

share|improve this question
    
your desired output is not ordered by date, but you describe that you want it to be? – dognose Jan 9 '13 at 6:10
    
please show structure of rounds table – Bhavik Shah Jan 9 '13 at 6:11
    
@Bhavik - UPDATED with rounds table structure above will this suffice? – Bill Chambers Jan 9 '13 at 6:18
    
what will be the score? I dont understand which column's data you are considering as score – Bhavik Shah Jan 9 '13 at 6:22
    
@Bhavik, the score would be the differential column, good question. – Bill Chambers Jan 9 '13 at 6:24
up vote 0 down vote accepted

There are couple of steps that you will have to follow.

1) Sort the results with score(differential) and get the ids of the six records having lowest score.

$sql4 = "SELECT * FROM rounds WHERE userID='$userID' ORDER BY differential LIMIT 20";
$result4 = mysql_query($sql4) or die(mysql_error());
$total_rounds = mysql_num_rows($result4);

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 { $score_count = 10; }

$idsArray = array();
for($i=0; $i<$score_count; $i++){
    $dets = mysql_fetch_array($result4);
    $idsArray[] = $dets['roundID'];
}

2) Loop over records and match the id in array that you have made by first step. If id matches apply CSS otherwise not.

$sql4 = "SELECT * FROM rounds WHERE userID='$userID' ORDER BY date DESC LIMIT 20";
$result4 = mysql_query($sql4) or die(mysql_error());

while($dets = mysql_fetch_array($result4)){
    if(in_array($dets['roundID'], $idsArray)){
        //apply CSS
        //display details here
    }
    else{
        //display details here
    }
}

Note: You should stop using mysql_* now. Its highly recommended by experts to use mysqli_* instead

share|improve this answer
    
this is it, makes complete sense. Thanks for your diligent work and quick response. Marking it as accepted. – Bill Chambers Jan 9 '13 at 6:53
up vote 0 down vote

I will take your example of $total_rounds = 16 my $score_count would be 6.

$total_rounds = 16;
$score_count = 6 // comes from the if-else loop you already have

// now we loop over the result
// the css is applied to every odd result, until $score_count is not 0

$counter = 0;
while( ( $data = mysql_fetch_assoc( $result4 ) ) !== false ) {
    if( ( $counter % 2 ) && $score_count ) {
        echo $data['date']; // apply your css here
    } else {
        echo $data['date'];
    }
    $counter++;
    $score_count--;

}

Hope this helps.

share|improve this answer
    
thanks its a start but the highlighted row may not always be the odd numbers as I showed in my desired output. That was stupid of me, I should have mixed it up, I only did that for ease of computation in my mind. Any further thoughts? – Bill Chambers Jan 9 '13 at 6:22
    
btw, what if two dates has same score? This is also possible. Isnt it? – Bhavik Shah Jan 9 '13 at 6:24
    
@BillChambers hmm..but you can't just go highlighting any row at will. There must be a logic according to which the rows are highlighted. Please tell us that condition.(Some filters) – web-nomad Jan 9 '13 at 6:25
    
Yes that is also possible if one plays two rounds of golf in the same day, which can happen. – Bill Chambers Jan 9 '13 at 6:26
    
@Pushpesh, the logic, trying to be as clear as possible is, SELECT the latest rounds (max 20), run total rounds through if-elseif-else statement to figure out the number of THE LOWEST DIFFERENTIALS in which to highlight in my date ordered output. – Bill Chambers Jan 9 '13 at 6:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.