通过php / android在排序的mySQL表中的正确位置插入数据

Question

I have found a ton of information on this subject, but its overload. I can't seem to find exactly the solution I am looking for, all I find is general stuff.

I have a mySQL table setup like this: rank | name | score

The android application only knows the name and score when inserting. I use php, and httppost to insert the data. I know how to do that. What I want to do is insert the data based on the score and have the rank assigned automatically also based on the score.

Say I have these values already in the database:

1. Tiff 1000
2. Allen 800

and I want to insert the values "Jake 900"

How do I get the resulting table to be:

1. Tiff 1000
2. Jake 900
3. Allen 800

Preferably I don't want the phone or tablet to be the thing sorting the data, because I know how to download the table, insert the info, resort it, and upload again, but I don't want the service hanging for a while even if it is in the background.

Solutions?

Answer 1

dont sort the table, sort the results of the query:

SELECT username, score FROM yourTable ORDER BY score DESC

just a a note: if you want the mySql to add the column of rank:

SET @rank = 0, @prev_val = NULL; 
SELECT rank, username, score 
      FROM (SELECT @rank := IF(@prev_val=score,@rank,@rank+1) AS rank,
                   username AS username,
                   @prev_val := score AS score FROM yourTable  ORDER BY score DESC)  
        AS result 

Do not store the rank in the table because if you do, you will need to update every lower rank row in the table everytime a new score is inserted.