advanced sorting help needed!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
speedamp
Forum Commoner
Posts: 45
Joined: Tue Apr 29, 2003 3:59 pm

advanced sorting help needed!

Post 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
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

...

Post 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 :)
Last edited by Calimero on Thu Oct 07, 2004 9:54 am, edited 1 time in total.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Something simple like:

Code: Select all

SELECT 
   SUM(score)
FROM
    events
ORDER BY
   score
LIMIT
    7
...might get you started.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

can you post the table structure, because I'm not sure SUM or any "normal" function can really help here..
speedamp
Forum Commoner
Posts: 45
Joined: Tue Apr 29, 2003 3:59 pm

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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
Post Reply