...

View Full Version : subquery without mysql subquery?



phpmysqlnewbie
05-28-2005, 03:42 AM
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:

1. find the last 20 rounds score (I am already done that in my SELECT statement).
2. from my result I want to sort the score field. - Need help here.
3. pick the 10 best scores (from the score field). - Need help here.
4. 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 ################

SeeIT Solutions
05-28-2005, 04:25 AM
The best option by far would be a subquery.

With the handicap script try this. It really isn't any different but it only formats strings once



$avgscore = sprintf("%.0f",($totarray0/$numrows));
$avgrating = sprintf("%.0f",($totarray1/$numrows));
$avgslope = sprintf("%.0f",($totarray2/$numrows));

echo "Average score of last $numrows rounds: ".$avgscore."<br>";
$myavghcp = (($avgscore - $avgrating) * 113 / $avgslope * 0.96);
$myavghcp_formatted = sprintf("%.1f",$myavghcp);
echo "My Handicap: <b>$myavghcp_formatted</b>";



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum