0

Possible Duplicate:
MySQL get row position in ORDER BY

I have a table of users and I want to order them by a column named crown. I then wanted to find out where they are in the list but not totally sure on how to do it. I have tried to Google it but not totally sure on what to type in. (Crown has INT input.)

So I have the first line:

mysql_query("SELECT * FROM users ORDER by crown DESC");

How would I then find out where a user is in the list while ordered by crown?

Thanks in advance.

Community
  • 1
  • 1
Sam Ham
  • 111
  • 2
  • 16
  • Without getting into the PDO vs. mysql or selecting * issues, it sounds like you're really looking for how to do this specifically with your SQL query, rather than looping through the results in PHP, correct? – David Grenier Oct 07 '12 at 13:26
  • Well I want to get the result into PHP so I can show the user where they are in the list. – Sam Ham Oct 07 '12 at 13:28

1 Answers1

0

I'd do something like this, assuming you can get the user's crown value.

SELECT `user_var_you_want`,
       (SELECT COUNT(*) FROM `users` WHERE `crown` > $user_crown_value)+1 AS `position`
FROM `users`
WHERE `user_var_you_want` = $search_value

Or if you can't get their crown value:

SELECT `user_var_you_want`,
       (SELECT COUNT(*) FROM `users` WHERE `crown` > (SELECT `crown` from `users` WHERE `user_var_you_want` = $search_value)+1) AS `position`
FROM `users`
WHERE `user_var_you_want` = $search_value
MrLore
  • 3,759
  • 2
  • 28
  • 36
  • The crown value is already in there. I want it to then put the number in order then find out their position in the list. – Sam Ham Oct 07 '12 at 13:37
  • Indeed, but if you want to find out their position in the list, you need to retrieve it first so you can find out how many are above them then count it, then you have their position, that is what my queries above do. By `assuming you can get the user's crown value` I meant whether you have it already saved as a variable on the page you are performing the query on. If you do, use the first one, if not, use the second one. – MrLore Oct 07 '12 at 13:39
  • So you are saying in the end I can get it to say which position they are in. So if they are in 4th position it will say "4"? – Sam Ham Oct 07 '12 at 13:41
  • With the edits I just made, it should, try it out. – MrLore Oct 07 '12 at 13:44
  • In the end I want to show it in PHP... – Sam Ham Oct 07 '12 at 14:05
  • Yeah, an array of the variable you put in and their position. – MrLore Oct 07 '12 at 14:51
  • I really don't understand what going on. I just an output in PHP which will say the users position. Do you know how I can do that exactly? – Sam Ham Oct 07 '12 at 14:53
  • Remove ``user_var_you_want`,` from the first line of the query. – MrLore Oct 07 '12 at 14:54
  • Still dont know how to get it to output in PHP.. – Sam Ham Oct 07 '12 at 14:56