4

Okay so I have a table that has the following

KEY   username   password   score

The above columns are not in any specific order.

I want to send my Database a username and have it send me back what rank that user name is based on its score. So for example if I had 10 people in there and the 3rd person in has the highest score. When I pass the 3rd persons username in I want it to send back 1.

Is this possible?

I have been trying things like this

$result = mysql_query("SELECT * FROM tablename where username='$username' ORDER BY score DESC");

but it doesnt seem to give me the row number

John Woo
  • 258,903
  • 69
  • 498
  • 492
DotNetRussell
  • 9,716
  • 10
  • 56
  • 111
  • 2
    You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are probably also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. – Quentin Feb 28 '13 at 13:45
  • @JohnConde I am asking it again because it wasn't answered and it got buried so the likely hood of getting an answer wasn't good. I deleted the other one and asked again. I don't see an issue in that. – DotNetRussell Feb 28 '13 at 13:53
  • 1
    SO doesn't like when users do this. You can always edit your question to improve it and that would bump it up automatically. Now all of the history is gone which would have been helpful to other users. – John Conde Feb 28 '13 at 13:54
  • It wasn't useful to anyone because it didn't answer the question. It was only mud in the water. I did just edit it and it didn't refresh it. Also, you just reposted the same answer so it didn't delete the history of anything. – DotNetRussell Feb 28 '13 at 13:56
  • 2
    @JohnConde I don't think a delete/repost is bad per se if the repost is actually improved. (Doing it merely to get rid of downvotes if the new question isn't any better isn't great though.) – millimoose Feb 28 '13 at 14:04
  • @Quentin the chances are slim of an injection attack. It is a DB for a mobile app. There is never any time that the user sees a URL to the PHP page. The only time the user interacts with the DB is through the username and password. I sterilize the username and password boxes prior to sending it off to the php page. – DotNetRussell Feb 28 '13 at 14:07
  • @AMR — Security through obscurity isn't security. Nor is it any reason to use deprecated libraries or sloppy coding techniques. – Quentin Feb 28 '13 at 14:08
  • @Quentin I am only learning. If there was a magic book out there that told me every proper coding practice I would use it. Currently I am using the w3Schools.com website. – DotNetRussell Feb 28 '13 at 14:12
  • W3Schools have no say in the standards of the web. They are a crappy, out of date tutorial site with good SEO and a lot of reflected glory from having the first two letters of their name being the same as the W3C. – Quentin Feb 28 '13 at 14:15
  • @Quentin Anyone can criticize but have you offered another solution? Instead of being insulting, if you have a problem with it then why not offer an alternative solution. – DotNetRussell Feb 28 '13 at 14:18
  • @AMR — There are a lot of links in my original comment. – Quentin Feb 28 '13 at 14:19
  • Then just say that next time – DotNetRussell Feb 28 '13 at 14:20

2 Answers2

3

This will handle ranks that have the same score.

SELECT  d.*, c.ranks
FROM
        (
          SELECT    Score, @rank:=@rank+1 Ranks
          FROM
                  (
                      SELECT  DISTINCT Score 
                      FROM    tableName a
                      ORDER   BY score DESC
                  ) t, (SELECT @rank:= 0) r
        ) c 
        INNER JOIN tableName d
            ON c.score = d.score
// WHERE   d.username = 'Helen'

for example

KEY     username    password    score   Ranks
1       Anna        123         5       3
2       Bobby       345         6       2
3       Helen       678         6       2
4       Jon         567         2       4
5       Arthur      ddd         8       1

for better performance, add an INDEX on column Score,

ALTER TABLE tableName ADD INDEX (Score)
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • FYI, they only want to get one user at a time. When they asked the same question last night we were able to draw that out of them but they apparently forgot to mention that again when asking this same question...again. – John Conde Feb 28 '13 at 13:55
  • 1
    @JohnConde I know, the query handles duplicate. If I didn't use variables, *in my example*, `Bobby` and `Helen` would have different rank. – John Woo Feb 28 '13 at 13:57
  • Thanks for the help @JW when I get home from work I will try your solution. – DotNetRussell Feb 28 '13 at 14:13
  • 1
    if you have any confusion, don't hesitate to ask. `:)` – John Woo Feb 28 '13 at 14:36
  • Is it possible to return users with a score 0 in the results, but exclude them from the ranking? – user1019144 Mar 06 '15 at 04:00
2
SELECT 
    (SELECT COUNT(*)+1 FROM tablename WHERE score > t.score) as rank,
    * 
FROM
     tablename t
where 
     username='$username'

The ORDER BY in your query is useless since you're only returning one row.

John Conde
  • 217,595
  • 99
  • 455
  • 496