Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    May 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subquery without mysql subquery?

    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 ################

  • #2
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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

    PHP Code:
    $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>"


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •