View Full Version : Finding a users position within a query!?

10-23-2009, 12:38 PM
I have a script which prints out the position of a user in a while loop like this:

$sql = "SELECT u.fname, u.lname, u.userid, up.points FROM users u
INNER JOIN ( SELECT SUM(point) points, userid FROM bonuspoints
GROUP BY user ) up ON u.userid = up.userid
ORDER BY up.points DESC";

$result = mysql_query($sql);

$x = 0;
$i = 0;
$prev = 0;
$r_count = 0;

while($row = mysql_fetch_array($result)){

$curr = $row['points'];

if ($curr != $prev)
$i = $x + 1;

echo ''.$i.' '.$row['fname'].' '.$row['lname'].'<br>';

$prev = $curr;


Now, this works like a charm, but I want to, first of all, to print out only the users current placement and not all the others...

As it is now all users are shown in a list...

The tricky part to this is that the total amount of points the users have is calculated in the query:

SELECT SUM(point) points

How do i figure out what number the user is within tha or a query?!?!?

Hope this makes sense and thanks in advance...

10-23-2009, 05:14 PM
Since your query is what is determining the ranking, you can't just specify WHERE userid = 12345. So I guess you keep the query as-is and only echo anything when the userid matches current userid, at which point you echo and break out of the loop.

mr e
10-23-2009, 09:18 PM
You could also query for a total of all users with more points than the current user, increment by one and that should be their position