[RESOLVED] Rank players based on calculation

[esiason14]

Hi, I'm trying to rank football players based on calculations from my query:

My query

$sql = "SELECT p.lname, p.fname, SUM(pass_yd) AS sumpassyd, SUM(pass_att) as sumpassatt, SUM(pass_cmp) as sumpasscmp, SUM(rush_yd) AS sumrushyd, SUM(total_td) AS sumtotaltd, SUM(rush_att) AS sumrushatt, SUM(pass_td) as sumpasstd, SUM(ps.qb_in) AS sumqbint, ps.player_id, ps.year
  FROM playerstats ps, players p
  WHERE ps.player_id = p.player_id
  AND p.position_id = 1  

  AND ps.year = 2005
  GROUP by ps.player_id, ps.year
  ORDER by sumpassyd DESC";

My calculation

while ($row = mysql_fetch_array($result))
{
	$row['ffr_points'] = (($row['sumpassyd'] * 0.034) + ($row['sumpasstd'] * 4) + ($row['sumqbint'] * -2) + ($row['sumrushyd'] * 0.067) + ($row['sumtotaltd'] * 6));

I would like to sort the players based on the results of $row['ffr_points'] and display something like this:

print "$fname $lname is ranked number"$playerRank"";
}

Any help would be appreciated

[mjax]

A PHP way to do this would be:

$rows = array();
while ($row = mysql_fetch_array($result))
{
  $row['ffr_points'] = (($row['sumpassyd'] * 0.034) + ($row['sumpasstd'] * 4) + ($row['sumqbint'] * -2) + ($row['sumrushyd'] * 0.067) + ($row['sumtotaltd'] * 6)); 
  $rows[] = $row;
}

usort($rows, 'sort_rows_by_points');

$playerRank = 0;
foreach($rows as $row)
{
  $playerRank++;

  print "$row[fname] $row[lname] is ranked number $playerRank";
}

function sort_rows_by_points($a, $b)
{
  if($a['ffr_points'] < $b['ffr_points']) return 1;
  if($a['ffr_points'] > $b['ffr_points']) return -1;
  return 0;
}

[esiason14]

Thanks, mjax. Thats perfect. I worked on this for a couple of hrs, but just couldnt get it right.
Is there a way to account for players with the same pts total and set their rank accordingly?

[esiason14]

I'm still trying to find out how to account for the players with the same scores (ties). Can anyone point me in the right direction. This is killing me.

[mjax]

esiason14 wrote:

I'm still trying to find out how to account for the players with the same scores (ties). Can anyone point me in the right direction. This is killing me.

How would you like to rank players with the same score?

[esiason14]

Ideally, something like this:

1.   Daunte Culpepper	318.15
2.   Jon Kitna	         264.85
3.   Donovan McNabb	256.6
4t.  Joey Harrington	228.8
4t.  Jake Delhomme	 228.8
6.   Tommy Maddox	209.9
7t.  Jake Plummer	  193.6
7t.  Byron Leftwich	   193.6
9.   Kerry Collins	  180.4
10. Joey Harrington	  178.4

but if thats too much of a pain, this would do I guess

1.   Daunte Culpepper	318.15
2.   Jon Kitna	         264.85
3.   Donovan McNabb	256.6
4t.  Joey Harrington	228.8
4t.  Jake Delhomme	 228.8
5.   Tommy Maddox	209.9
6t.  Jake Plummer	  193.6
6t.  Byron Leftwich	   193.6
7.   Kerry Collins	  180.4
8. Joey Harrington	  178.4

[mjax]

Change the foreach() loop into this:

$playerRank = 1;
for($i = 0 ; $i < count($rows) ; $i++)
{
  $row = $rows[$i];

  if(($i < count($rows)-1 && $row['ffr_points'] == $rows[$i+1]['ffr_points']) || ($i > 0 && $row['ffr_points'] == $rows[$i-1]['ffr_points'])) 
  {
    $tie = 't';
  } 
  else 
  {
    $tie = '';
  }

  if($i > 0 && $row['ffr_points'] != $rows[$i-1]['ffr_points'])
  {
    $playerRank = $i+1;
  }

  print "$playerRank$tie. $row[fname] $row[lname] $row[ffr_points]<br/>\n";
} 

[esiason14]

Yep, thats it! Thanks once again!!