SELECT idscore, score, rating, slope FROM t1, t2, t3 WHERE t1.courseid = t2.idcourseinfo AND t1.nocount = 'N' AND t1.playerid = t3.signupID AND t3.username = 'myusername' ORDER BY idscore DESC LIMIT 20;
+---------+-------+--------+-------+
| idscore | score | rating | slope |
+---------+-------+--------+-------+
| 105 | 88 | 72.5 | 122 |
| 104 | 85 | 71.7 | 127 |
| 103 | 85 | 71.7 | 122 |
| 102 | 87 | 70.8 | 122 |
| 101 | 80 | 70.8 | 122 |
| 100 | 86 | 70.9 | 119 |
| 97 | 86 | 71.7 | 127 |
| 96 | 85 | 71.7 | 127 |
| 95 | 81 | 70.8 | 123 |
| 94 | 87 | 71.8 | 125 |
| 93 | 82 | 72.5 | 122 |
| 92 | 83 | 69.8 | 119 |
| 91 | 76 | 69.6 | 111 |
| 89 | 89 | 71.8 | 125 |
| 88 | 80 | 69.8 | 119 |
| 87 | 89 | 72.5 | 122 |
| 85 | 86 | 69.8 | 119 |
| 84 | 80 | 69.8 | 119 |
| 83 | 82 | 69.8 | 119 |
| 82 | 81 | 69.8 | 119 |
+---------+-------+--------+-------+
20 rows in set (0.04 sec)
Greetings,
In my php how can I do this without using the mysql subquery function?
This is what I am trying to do:
- find the last 20 rounds score (I am already done that in my SELECT statement).
- from my result I want to sort the score field. - Need help here.
- pick the 10 best scores (from the score field). - Need help here.
- then do my formula below to get my handicap. - Need improvement is available.
//############ Code Starts ################
$totarray0 = 0;
$totarray1 = 0;
$totarray2 = 0;
$myarray = array();
$query = "SELECT * FROM t1, t2, t3
WHERE t1.courseid = t2.idcourseinfo
AND t1.nocount = 'N'
AND t1.playerid = t3.signupID
AND t3.username = '$HTTP_SESSION_VARS[id]'
ORDER BY idscore DESC LIMIT 20";
$result = mysql_query($query);
$numrows = mysql_num_rows($result);
for($i=0; $i<$numrows; $i++) {
$row = mysql_fetch_array($result);
$myarray[$i][0] = $row["score"];
$myarray[$i][1] = $row["rating"];
$myarray[$i][2] = $row["slope"];
$totarray0 = $totarray0 + $myarray[$i][0];
$totarray1 = $totarray1 + $myarray[$i][1];
$totarray2 = $totarray2 + $myarray[$i][2];
}
for($i2=0; $i2<count($myarray); $i2++) {
}
$avgscore = $totarray0/$numrows;
$avgrating = $totarray1/$numrows;
$avgslope = $totarray2/$numrows;
echo "Average score of last $numrows rounds: ".sprintf("%.0f",$avgscore)."<br>";
$myavghcp = ((sprintf("%.0f",$avgscore) - sprintf("%.1f",$avgrating)) 113 / sprintf("%.0f",$avgslope) 0.96);
$myavghcp_formatted = sprintf("%.1f",$myavghcp);
echo "My Handicap: <b>$myavghcp_formatted</b>";
//############ Code Ends ################
