Selecting Top 5

[vaavi8r]

I want to be able to select the top five records from my database based upon a sum of one of the columns.

I have modified a code that I have used in the past to output all of the records, based upon their record # and it appears as follows:

Code:

include(MYSQL_CONNECT_INCLUDE); 

/* Select all pilots */ 
$query = "SELECT * FROM pilots ORDER BY pilot_num ASC"; 
$result = mysql_query($query); 

/* Determine the number of pilots */ 
$number = mysql_numrows($result); 

if ($number > 0) { 
   /* Print roster header*/ 
   print "<table>"; 
   print "<tr>"; 
   print "<td bgcolor=#000080 width=85 height=12 align=left><font face=Arial color=#FFFFFF size=2><b>NUMBER</b></font></td>"; 
   print "<td bgcolor=#000080 width=120 height=12 align=left><font face=Arial color=#FFFFFF size=2><b>NAME / EMAIL</b></font></td>"; 
   print "<td bgcolor=#000080 width=130 height=12 align=left><font face=Arial color=#FFFFFF size=2><b>CITY</b></font></td>"; 
   print "<td bgcolor=#000080 width=93 height=12 align=left><font face=Arial color=#FFFFFF size=2><b>COUNTRY</b></font></td>"; 
   print "<td bgcolor=#000080 width=93 height=12 align=left><font face=Arial color=#FFFFFF size=2><b>FLIGHT TIME</b></font></td>"; 
   print "<td bgcolor=#000080 width=75 height=12 align=left><font face=Arial color=#FFFFFF size=2><b><center>STATUS</center></b></font></td>"; 
      print "<td bgcolor=#000080 width=75 height=12 align=left><font face=Arial color=#FFFFFF size=2><b><center>LOG BOOK</b></center></font></td>"; 
   print "</tr>"; 

   /* Get pilots info */ 
   for ($i=0; $i<$number; $i++) { 
           $num = mysql_result($result,$i,"pilot_num"); 
           $name = mysql_result($result,$i, "name"); 
           $city = mysql_result($result,$i, "city"); 
           $country = mysql_result($result,$i, "country"); 
           $status = mysql_result($result,$i, "status"); 
      $id = mysql_result($result,$i, "pilot_id"); 
      $email = mysql_result($result,$i, "email"); 
                                $log = mysql_result($result,$i, "log"); 

        /* Calculate flight hours */ 
  $query_hours = "SELECT sec_to_time(sum(time_to_sec(t2.duration))) AS duration_sum FROM pilots t1, reports t2 WHERE t1.pilot_id=$id AND t1.pilot_id=t2.pilot_id"; 
      $result_hours = mysql_query($query_hours); 

      if (mysql_numrows($result_hours) > 0) { 
     $time = mysql_result($result_hours,0,"duration_sum"); 
  } 
        ?> 
    <table border="1"> 
       <tr> 
       <td bgcolor=#F0F8FF width=78 height=12 align=left><font face=Arial size=2 color=#000080><? echo $num; ?></font></td> 
       <td bgcolor=#F0F8FF width=120 height=12 align=left><font face=Arial size=2 color=#000080><a href="mailto:<? echo $email; ?>"><? echo $name; ?></a></font></td> 
       <td bgcolor=#F0F8FF width=130 height=12 align=left><font face=Arial size=2 color=#000080><? echo $city; ?></font></td> 
       <td bgcolor=#F0F8FF width=93 height=12 align=left><font face=Arial size=2 color=#000080><? echo $country; ?></font></td> 
       <td bgcolor=#F0F8FF width=93 height=12 align=left><font face=Arial size=2 color=#000080><? echo $time; ?></font></td> 
       <td bgcolor=#F0F8FF width=73 height=12 align=left><font face=Arial size=2 color=#000080><? echo $status; ?></font></td> 
  <td bgcolor=#F0F8FF width=73 height=12 align=left><font face=Arial size=2 color=#000080><center><a href="<? echo $log; ?>">Flightlog</a></center></font></td> 
</tr> 
</table> 
        <?    

   } 

   print "</table>"; 
} 

What I want to do is be able to select the top five records based upon the value of the variable $time and display them on the front page of my website.

I am a relative newbie when it comes to this and cannot find any information on how to do this.

Any help wolud be appreciated.

Thanks
Todd Lucas

[reflash]

I'm a serious noob at this myself (only working with PHP for 5 days), so I can't give you any (workable) code. That being said, it would seem to me that after the table header is printed & variables are loaded from the select query, do a descending array sort based on the field you're interested in, and then use a "for" loop incremented from 1 to 5 to print - using the loop counter as the array index. Hope this helps.

[leatherback]

Hi,

It is a bit unclear what exactly you want to do (Or I am just too tired to read properly) but you need to sort the pilots based on their time: Duration sum, right?

So, something like this is what you would be looking for (Dodgy coding, do not know your datastructure..):

      $query_hours = "SELECT *, sec_to_time(sum(time_to_sec(t2.duration))) AS duration_sum FROM pilots t1, reports t2 WHERE t1.pilot_id=t2.pilot_id order by duration_sum limit 5";
          $result_hours = mysql_query($query_hours);