CODEWITHSUNDEEP
phpmysql
ma3x
ma3x

Reputation: 77

Calculate user rank based on two columns

I did wrote a Web Service for a Android game with PHP. The query is used for calculating user's rank is like this:

$rank = mysqli_fetch_row(mysqli_query($db, "SELECT FIND_IN_SET(`score`, (SELECT GROUP_CONCAT(`score` ORDER BY `score` DESC) FROM `scores`)) AS `rank` FROM `scores` WHERE `user_id` =  '{$user_id}'"));

So, what is the problem? Suppose user_a submit his score (for example 1000) and $rank equals to 1, then if user_b submit the same score as user_a, the $rank will be 1 again. I don't want this, in the case of score equality, first user must have the higher rank. So I added a new integer column for submit_date timestamp to scores table and tried to manipulate the query and unfortunately since i'm not professional in SQL, My efforts failed.

Update: I won't store the rank itself in the database. The scores table structure looks like this:

`_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`score` int(11) NOT NULL,
`submit_date` int(11) NOT NULL

The user_id is unique. If a user didn't submitted his score before, a new record created. Else his record will be updated (in case new score is higher than old one).

What should i do?

Upvotes: 1

Views: 153

Answers (1)

Samir Karmacharya
Samir Karmacharya

Reputation: 890

    If two user with same score and do display old user at top.
    you can add on update date ie when the score is update the current date time will be inserted on that field.
    then when you invoke record from the data base retrieve according to rank and update date
    like: 
    SELECT * FROM test ORDER BY rank DESC, update_date ASC

from this you can get two field can order at a time

table for above result

Upvotes: 1

Related Questions