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:
and I want to insert the values "Jake 900"
How do I get the resulting table to be:
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?
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.
Why don't you use "ORDER BY DESC score" in the SQL query getting the results?