I'm really struggeling with this.
I'm trying to create golf leaderboard and want to show the current users placement on the the leaderboard +- 5.
My problem is how to get the placement out of the query. The problem is that the placement is calculated within the query like this:
$sql = "SELECT u.fname, u.lname, u.new_userid, u.country, user_points.points FROM ".$prefix."_users cu
INNER JOIN ".$prefix."_users u ON u.new_userid = cu.new_userid
INNER JOIN ".$prefix."_GA_users ON u.new_userid = ".$prefix."_GA_users.userid
INNER JOIN ( SELECT AVG(point) points, userid FROM ".$prefix."_GA_leaderboard
WHERE seasonid = $seasonid
GROUP BY userid ) user_points ON cu.new_userid = user_points.userid
WHERE ".$prefix."_GA_users.aproved = 1 ORDER BY user_points.points";
Then I show the leaderboard like this in a loop:
while($row = mysql_fetch_array($result)){
$userid = $row['new_userid'];
$fname = $row['fname'];
$lname = $row['lname'];
$ucountry = $row['country'];
$sql2 = "SELECT * FROM ".$prefix."_geo_countries WHERE con_id='$ucountry'";
$result2 = mysql_query($sql2);
$row2 = mysql_fetch_array($result2);
$curr = $row['points'];
if ($curr != $prev)
{
$i = $x + 1;
}
echo '<tr style="background-color:#' .((++$r_count %2 == 0) ? 'FFFFFF' : 'EEEEEE'). '">';
echo '<td align="center" valign="middle"><input name="placement[]" type="text" readonly="true" value="'.$i.'" style="font-size:11px;width:16px;text-align:center;border:none;background-color:transparent;font-family:lucida grande,tahoma,verdana,arial,sans-serif;"/></td>';
echo '<td align="left" valign="middle"> ';
if(!$row['country']) {
echo '<img src="images/lang/nocountry.png"> ';
} else {
echo '<img src="images/lang/'.$row2['iso'].'.png"> ';
}
echo ''.$fname.' '.$lname.'</td>';
echo '<td align="center" valign="middle"><div style="text-align:center;">' . number_format($row['points'], 2) . '</div></td>';
echo '</tr>';
$prev = $curr;
$x++;
}
echo '</table>';
echo '</div>';
This works great, but if the current user is placed as #543 he would have to scroll a long way to find him self, so the ideal would be to just get his current position +-5 players, but... How do I do that... How do I get the users current position from the above query...
Hope this is understandable and somebody can help... PLEASE :-)
