sorting results help

[michaelowen]

hello,

Here is what i need to do and am a little stuck.

I have a database the keeps calculated points for 15 different events (per entry). I need to take all 15 events and total ONLY the top 7 scores. How can i do that?

I have it looping dynamically already, and this is going to make my head explode.

  $run_query = "SELECT * FROM xxxx WHERE sex = 'male' and eight_place > 0 order by eight_place asc";
  $run_result = mysql_query($run_query);
  $numrows = mysql_numrows($run_result);
    $i=1;
    while($run_row = mysql_fetch_array($run_result)){
            $mydataorder = $run_row[user] + $i;
            echo "<input type=\"hidden\" size=\"5\" name=\"order\" value=\"$mydataorder\">".$mydataorder."";
            echo "&nbsp;&nbsp;<input type=\"hidden\" name=\"first_name\" value=\"$run_row[first_name]\">".$run_row[first_name]."";
            echo "&nbsp;&nbsp;<input type=\"hidden\" name=\"last_name\" value=\"$run_row[last_name]\">".$run_row[last_name]."";
            echo "&nbsp;&nbsp;<input type=\"hidden\" name=\"xxxx_id\" value=\"$run_row[xxxx_id]\">".$run_row[xxxx_id]."";
            echo "&nbsp;&nbsp;<input type=\"hidden\" name=\"eight_place\" value=\"$run_row[eight_place]\">".$run_row[eight_place]."";
            echo "&nbsp;&nbsp;<input type=\"hidden\" name=\"eight_time\" value=\"$run_row[eight_time]\">".$run_row[eight_time]."";
            $mydatapoint = ROUND(1000-($mydataorder-1)*(1000/$numrows));
            echo "&nbsp;&nbsp;<input type=\"hidden\" name=\"eight_points\" value=\"$mydatapoint\">".$mydatapoint."";
            $mydataoverall = $run_row[one_points]+$run_row[two_points]+$run_row[three_points]+$run_row[four_points]+$run_row[five_points]+$run_row[six_points]+$run_row[seven_points]+$mydatapoint;
            echo "<br>";
            $sql = "UPDATE xxxx SET eight_points='$mydatapoint',overall_points='$mydataoverall' WHERE xxxx_id='$run_row[xxxx_id]'";
           $result = mysql_query($sql) or die('error: '. mysql_error());
           $i++;
          }

how can i do another sorting internally PER each entry and calculate a new field "top_scores" of just the top seven point values (e.g. one_points, two_points,etc..)?

-Michael

[MrPixel]

Not entirely sure if I understand what you mean, and therefore if this addresses your problem, but it sounds like all you need is a LIMIT clause in your SQL:

SELECT * FROM xxxx
WHERE sex = 'male' AND eight_place > 0
ORDER BY eight_place ASC LIMIT 7

[wilsonodk]

Look into using SUM(). It may help you out as well.