Table Results with Rank

[samtwilliams]

Hi All,

 

Some assistance required if possible , i have designed a database in which results of golf are kept. The position in which people appear within the table depend on a percentage of games won, lost or drawn. I then display the results ordered by % which gives me a leader table. The problem comes when I am ranking each players position. It's easy to do if each player has a different percentage but when more than one player is exactly the same I want to rank them the same. Example below.

 

1st  Sam Williams|100%

2nd  Bill Gates|99%

2nd  Steve Jobs|99%

3rd  Will Smith|98%

4th  Tony Davies|97%

5th  James Helmand|96%

 

The question is how i achieve a ranking like that which will rank duplicates the same and then carry on after in numbered order.

 

My results a retrieved from my database using this query;

 

$query_board = "SELECT * FROM winter_league WHERE played >= $to_qualify ORDER BY winter_league.percentage DESC, played DESC, win DESC, draw DESC, loss DESC, surname ASC";
$result_board = mysql_query($query_board);

 

then displayed in a table like this;

 

 <?PHP while ($row_board = mysql_fetch_assoc($result_board)){ ?>
  <tr>
    <td height="25"><?PHP echo $row_board['forename'].' '.$row_board['surname']; ?></td>
    <td class="align_columns"><?PHP echo $row_board['played']; ?></td>
    <td class="align_columns"><?PHP echo $row_board['win']; ?></td>
    <td class="align_columns"><?PHP echo $row_board['draw']; ?></td>
    <td class="align_columns"><?PHP echo $row_board['loss']; ?></td>
    <td align="right" class="align_columns"><?PHP echo $row_board['percentage']; ?>%</td>
  </tr>
  <?PHP } ?>

 

Any Help would be most appreciated.

 

Thanks in advance.

Sam

 

[Zane]

What does your table structure look like.. and how are you calculating the percentages.

 

Also, right off the bat, I can tell you the solution to this is that you need your percentage decimal to go to the farthest decimal place possible, in order to get an accurate ranking.

 

Example...

 

2nd place == 99.86535758

3rd  place == 99.86531115

[samtwilliams]

Hi Zanus,

 

It has to stay as two decimal places as prizes are awarded to joints (strange I know as i originally coded it to perform that way).

 

Percentages are stored in the table as they are calculated monthly rather than on the fly. Table structure as follows;

 

wl_id     forename     surname     win     draw     loss     played     percentage     note     datetime_created

 

percentage is a float to two decimal places.

 

Sam

[Zane]

Ok, I see now.

 

I'm not too familiar with doing ranks like that, but I came across a tutorial that might help

http://www.fromdual.ch/ranking-mysql-results

[gizmola]

When you're fetching the rows, just use a variable that you increment whenever the $row_board['percentage'] is greater.

 

                   $rank++;
                   $lastpct = $row_board['percentage'];
               }
?>  
  
    
    
    
    
    
    
    %
  
  

 

 

[Zane]

Ah, wasn't thinking in terms of PHP, d'oh

 

along with gizmola's solution you can use this ordinal function to get your 1st 2nd 3rd,etc functionality

 

Straight from PHP.net manual http://www.php.net/manual/ro/function.is-numeric.php#99876

function ordinal($i='') {
  // a temporary value we can change, and keep the original value.
  $o=$i;

  // suffixes = 0th, 1st, 2nd, third == zeroth, first, second, third
  $s=array('th','st','nd','rd');

  // if input just so happens to be a string, we check to make sure it
  // still holds a numeric value and only acquire the last 2 numbers.
  // if it's not a string, nor an integer, we freak out and say no.
  if(!is_int($o))
    if(ctype_digit($o))
      $o=(int)substr($o,-2,2);
    else
      return(false);

  // basically, if $o is between 11 and 19, we use 'th'
  // otherwise we use the last digit and if it's over
  // 4 then we use 0 (for the $s array index).
  return($i.$s[($o%100>10&&$o%100}