Finding row # from an SQL query?

acidbox

I table called rankings. There is user_id, user_name, and user_rank.

What I would like to do is be able to select row # for a user_id when the table is sorted by user_rank DESC. The row # would reflect the rank that that user is in. Basically, I don't want to store the rank Number, I want to have it automatically assigned based on the user_rank. However, I don't want to have to loop through the entire table to do it. Anyone know an easy way to do this?

(linux, mysql)

jayant

when u r displaying records, just start a counter and have it to increment for each record.

$res = mysql_query("SELECT ....");
$i=0;
while($row = mysql_fetch_array($res)) {

$i++;
echo $i.$row['user_id'].$row['user_name'];

}

acidbox

Yes, I know how to do that, but the problem is that I need to know the number of the person before the array starts, because it affects what the other people display. I was trying not to go through the loop twice for such a simple query. thx though.

johnnyv

Could you be more specific?

You have a field called user_rank and you sort by desc so that the largest rank appears first?

Do you want the largest rank to be number one or the number of users?

from your second response, i guess you want to be able to say "you are No.1 out of 276"?

If you give more information we are more likely to be able to figure out what you want.

you can always do a mysql_num_rows() before mysql_fetch_assoc() to determine the number of ranks.

acidbox

Basically, if a user is logged in and they view the rank list, other users on the list that are a certain number above the logged in user's rank will show up with a special icon/color on their respective rows. For example, if you were viewing the list, and the user's rating was 10, the closest 5 users above a rating of 10 would show up a different color than everyone else.