Rank system in a query

[Mort]

Hey all

Trying to work out how to get a users rank in a voting system i have

basically the top 10 (or more) is displayed by the following

SELECT vl_cat_id, count(vl_username), vl_username, vl_whovoted FROM vote_log WHERE vl_cat_id = '$vote_cat' GROUP BY 'vl_username' ORDER BY 'count(vl_username)' DESC LIMIT $limit

anyways on the users page i need to show their rank in the voting chart, so how would i do the above, but with WHERE...AND vl_username = '$username' as well. But then to show which position in the query it was at?

Need to do it without working out the whole table, as there will potentially be out of quite a few members

 

[psyr33n]

Create an integer variable and increment it manually in a loop, either echoing it or assigning it to an array along with the database data.

A similar example:

/*
* MySQL example
*/

$query = mysql_query('SELECT vl_cat_id, count(vl_username), vl_username, vl_whovoted FROM vote_log WHERE vl_cat_id = \'' . $vote_cat . '\' GROUP BY \'vl_username\' ORDER BY \'count(vl_username)\' DESC LIMIT ' . $limit);
$i = 1;

while($row = mysql_fetch_array($query))
{
 echo $i, ' - ', $row['x'];
 $i++;
}

 

[Beansprout]

Aye, but that gets inefficient with a large number of members. I'd guess one way to do it is to cache the data in a table and periodically run a script to update it.

 

[psyr33n]

The appended limit and nature of the script (presumably top X ratings) made me think that it wouldn't really be necessary for caching or other performance-related measures.

However I'm slightly intrigued as to what's inefficient about incrementing a variable, even when in a large loop. I've not run into any sorts of performance issues doing this with pretty large loops (obviously with massive loops performance is hindered anyway.)

 

[Beansprout]

The OP is looking for a way around having to loop through every row basically - the LIMIT is just for the 'top 10' bit - he's asking about how to display a user's rank on their member page

I agree on the loop suggestion, that was what I suggested to him hehe