Page 1 of 1

advanced sorting help needed!

Posted: Thu Oct 07, 2004 9:13 am
by speedamp
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.


Code: Select all

$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_point
s]+$mydatapoint; 
            echo "<br>"; 
            $sql = "UPDATE xxxx SET  eight_points='$mydatapoint',overall_points='$mydat
aoverall' 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

...

Posted: Thu Oct 07, 2004 9:33 am
by Calimero
Count() each one separately - event,
and put (all) those results in one column.

next to do is order by that column desc limit 7, and in php add up those results.

The other way would be calculation inside mysql.


Sorry - SUM() is the function, my mistake :)

Posted: Thu Oct 07, 2004 9:47 am
by pickle
Something simple like:

Code: Select all

SELECT 
   SUM(score)
FROM
    events
ORDER BY
   score
LIMIT
    7
...might get you started.

Posted: Thu Oct 07, 2004 10:23 am
by feyd
can you post the table structure, because I'm not sure SUM or any "normal" function can really help here..

Posted: Fri Oct 08, 2004 9:37 am
by speedamp
ok, i see what you mean. me being too vague.

Here is an example of the db:

name|one_points|two_points|three_points|four_point|......
---------------------------------------------------------
bill|2|653|1000|64|......
ted|234|999|763|2|.....


There will be different point values, and i need to SUM only the highest 7 values for EACH person and enter that into the a field (e.g. total_overall)

Does this help clarify?

-Mke

Posted: Fri Oct 08, 2004 9:53 am
by infolock
this may be of help to you. read both (or maybe it's all 3?) pages and you should have no problems doing what you need...

http://www.onlamp.com/pub/a/onlamp/exce ... ndex3.html