MySQL/php help

Hey guys,

Quick question about mysql, let's say I want to pull data from a table, limit 100 results, where 'score' is highest, from high to low, I'd get results like this, perhaps:

name | score
------------------
tom | 50
joe | 45
bill | 40
.......

and so on. Now, what I would like to be able to do is easily find a given name's 'rank', which would be out of 100, sorting 'score' as I did above. 'tom' would be #1, for instance, Joe #2, and Bill #3.

What I'm wondering is, is there an easy way to make a mysql request to retrieve 'where name = "tom"' and also retrieve the number of rows from the top that the entry sits at, ordered by score highest to lowest.

Any help is appreciated, thanks a lot! :)

David

a query like this is what your looking for:

"SELECT name, score FROM mytable ORDER BY score ASC LIMIT 100"

Select name and score from table ordering them by score in ascending order from the first 100 rows.

Right, but I need to return the number which it ranks from that query. :)

use a while loop with a count increment

PHP Code:

---

$number = 1;
while(blahblahblah) {
     echo $number;
     // blah
     $number++;
} 


---

Code:

---

set @row = 0;
select
  @row := @row+1 'Index',
  name 'Name',
  score 'Score'
order by score desc
limit 100;

---