CODEWITHSUNDEEP
phpmysql
kira423
kira423

Reputation: 427

Getting row number from database when using where and order by statements

Ok I have tried several ways to do this but none of them work, so I have no code to show. But I will explain this the best I can.

I have a contest script, and I am wanting to add a line that will show the user which place they are in under the list of current winners. So I want the table to look like this.

-----------------------------------------
| Place |    User   | Prize | Completed |
|   1   | Someuser1 | $5.00 |     5     |
|   2   | Someuser2 | $2.50 |     3     |
|   3   | Someuser3 | $1.25 |     2     |
|   20  |    You    |  ---  |     1     |
-----------------------------------------

I have everything for the so called someusers, but I want to be able to count where the current user that is viewing the page can see where they stand in the contest. What kind of query could I run to get the place in which the current user is.

I hope that makes sense with no code.

Table structure

    Column   |  Type | Null |  Default
--------------------------------------
    id       |int(11)|  No  |   
    username |text   |  No  |   
    completed|int(11)|  No  |

Upvotes: 4

Views: 176

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562368

There are a couple of ways to do this. One is to count how many rows have a higher rank than the given row:

SELECT COUNT(*) AS Place, t.*
FROM TheTable t
LEFT OUTER JOIN TheTable t2 ON t.Prize < t2.Prize
GROUP BY t.id
HAVING Place <= 3 OR user = 'You';

Another is to use a session variable to track the ranking:

SELECT * FROM (
  SELECT (@row := COALESCE(@row, 0)+1) AS Place, t.*
  FROM TheTable t
  ORDER BY Prize DESC) r
WHERE Place <= 3 OR user = 'You';

Upvotes: 2

purpletree
purpletree

Reputation: 1943

Okay, from what I understand, there are two ways I can think of doing it.

  1. Have 2 queries. First query to get the top 3, then one to get the user place

    # First query to get top three

if (  user not in top three) {
     $db->prepare('SELECT whatever FROM table WHERE user = ?')->execute(array($user));
}

  2. Retrieve all the results, display the top three and display the one with the current user.

EDIT: Bill's answer seems better :)

Upvotes: 0

Related Questions