I'm having trouble with ranking the records in my table. It seems to be handling ties correctly, but for some reason the first row is being ranked as a zero, so the order I'm getting is coming out like 0, 2, 2, 4, 5. I've been playing about with this for ages and I've had no luck and I've been all over Google. I have a feeling I'm missing something really simple and obvious but I'll be damned if I can spot it.
Any help would be appreciated
<?php
$year = "$_POST[year]";
$gender = "$_POST[gender]";
$age = "$_POST[age]";
$event = "$_POST[event]";
// Formulate Query
$query = sprintf("SELECT * FROM (SELECT CONCAT(athletes.firstname,' ',athletes.lastname) AS athletename, athletes.athleteid, events.eventname, events.eventcode, meetings.meetingid, meetings.meetingname, meetings.location, meetings.meetingdate, meetings.year, DATE_FORMAT(meetings.meetingdate,'%%d %%b %%Y') AS date, results.performance, results.unit, results.eventid, results.position, results.heat, results.wind, agegroups.agegroupid, agegroups.agegroup
FROM results
JOIN athletes ON athletes.athleteid = results.athleteid
JOIN meetings ON meetings.meetingid = results.meetingid
JOIN events ON events.eventid = results.eventid
JOIN agegroups ON agegroups.agegroupid = results.agegroupid
WHERE results.performance = ANY (SELECT MAX(results.performance) from results WHERE meetings.year LIKE '%s' AND athletes.gender LIKE '%s' AND agegroups.agegroup LIKE '%s' AND results.eventid='%s' AND meetings.indoors='no' AND events.eventtype='field' GROUP BY results.athleteid)
ORDER BY results.performance desc, meetings.meetingdate asc) t1
GROUP BY t1.athleteid ORDER BY performance desc",
mysql_real_escape_string($year),
mysql_real_escape_string($gender),
mysql_real_escape_string($age),
mysql_real_escape_string($event));
// Perform Query
$result = mysql_query($query);
// Check result
if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}
// Use result
$rank = 0; $prevScore = 0; $naive_rank = 0; $aware_rank = 0; while ($row = mysql_fetch_assoc($result)) {
$naive_rank++; // always increment
if ($prevScore > $row['performance']) {
//whenever a non-tie occurs the aware rank catches up
$aware_rank = $naive_rank;
$rank = $naive_rank;
} else {
//whenever a tie occurs, just use the old aware rank
$rank = $aware_rank;
}
$prevScore = $row['performance'];
echo "<table valign='top' border='0' width='100%'>";
echo "<tr><td width='5%'> $rank </td>";
echo "<td width='20%'> <a href='profile.php?id=$row[athleteid]'> $row[athletename] </a> </td>";
echo "<td width='5%' align='right'> $row[performance] </td>";
echo "<td width='5%' align='left'> $row[unit] </td>";
echo "<td width='5%'> $row[wind] </td>";
echo "<td width='5%'> $row[position] </td>";
echo "<td width='5%'> $row[heat] </td>";
echo "<td width='5%'> $row[agegroup] </td>";
echo "<td width='30%'> <a href='meeting.php?id=$row[meetingid]'> $row[meetingname] </a></td>";
echo "<td width='15%' align='right'> $row[date] </td></tr>";
}
echo "</table>";
// Free the resources associated with the result set
mysql_free_result($result);
?>
EDIT
The table that is currently being displayed:
| # | Name | Perf. | Pos. | Age | Meeting | Location | Date |
--------------------------------------------------------------------
| 0 | Athlete A | 18.89 | 1 | U17 | Meeting A | Location | Date |
| 2 | Athlete B | 17.99 | 3 | U20 | Meeting C | Location | Date |
| 2 | Athlete C | 17.99 | 2 | V50 | Meeting F | Location | Date |
| 4 | Athlete D | 15.66 | 1 | U23 | Meeting H | Location | Date |
| 5 | Athlete E | 12.43 | 4 | U15 | Meeting B | Location | Date |
I think it's because you initialise $prevScore to 0, so for the very first athlete, $prevScore > $row['performance'] is false, and so $rank = $aware_rank, which starts at 0. At this point $naive_rank is 1.
Then for athlete number 2, $naive_rank is 2 so you get the jump.
You can either:
$prevScore to infinity (ie some huge number, so that for the first athlete $prevScore > $row['performance'] so that $aware_rank and $rank are set to 1 (instead of 0)); OR$aware_rank to 1 so that athelete 1 gets $rank = $aware_rank (since for the first athlete, $prevScore > $row['performance'] will never be true if the performance is always >= 0, since $prevScore is 0).