[RESOLVED] A rank users order by points

alexcsandru

I want to make an insert from table 'rank' , with number (rank) from the cod blow, to fild users.rank ...

set @rank := 0;
select @rank:=@rank+1 'rank', users.username, users.points from users order by points desc;

+------+-------------+--------+
| rank | username    | points |
+------+-------------+--------+
|    1 | ropy        |     98 |
|    2 | alexcsandru |     17 |
|    3 | bobo        |     17 |
|    4 | ovidiu      |      5 |
+------+-------------+--------+

How to do??

P.S.: to insert rank in users.rank table... and with a function reload_player_rank() of 30 in 30 minutes (to update new rank in table users.rank) .

A solution please,
Thank you!

djjjozsi

why do you need to insert the rank back to any table?

you have the 'rank' with that query.

alexcsandru

I have table users ... something link that

+----+-------------+----------+--------+--------------+--------+------+
| id | username    | password | banned | colonization | points | rank |
+----+-------------+----------+--------+--------------+--------+------+
|  1 | alexcsandru | ******   | N      |            1 |     17 |    0 |
|  2 | ovidiu      | ******   | N      |            1 |      5 |    0 |
|  3 | ropy        | ******   | N      |            1 |     98 |    0 |
|  4 | bobo        | ******   | N      |            1 |     17 |    0 |
+----+-------------+----------+--------+--------------+--------+------+

and i want to update rank table, where is 0 with rank users ordered by points...

I need to display that with $smarty template (users.username, users.points, users.rank) , and more function of the game!

I build a game browser based !

Have a solution ?

Sorry for my english! 😃

alexcsandru

i posted somthing and don't display it ! ... how it work's this forum ?

alexcsandru

I have table users ... something link that

+----+-------------+----------+--------+--------------+--------+------+
| id | username    | password | banned | colonization | points | rank |
+----+-------------+----------+--------+--------------+--------+------+
|  1 | alexcsandru | ******   | N      |            1 |     17 |    0 |
|  2 | ovidiu      | ******   | N      |            1 |      5 |    0 |
|  3 | ropy        | ******   | N      |            1 |     98 |    0 |
|  4 | bobo        | ******   | N      |            1 |     17 |    0 |
+----+-------------+----------+--------+--------------+--------+------+

and i want to update rank table, where is 0 with rank users ordered by points...

I need to display that with $smarty template (users.username, users.points, users.rank) , and more function of the game!

I build a game browser based !

Have a solution ?

Sorry for my english! 😃

bradgrafelman

alexcsandru wrote:
i posted somthing and don't display it ! ... how it work's this forum ?

Sometimes your posts might be hidden until a moderator approves it; this is because of our spam filter we use to keep advertisements off of the forums. Sorry about that! 🙂

As for your question.. is there really a need to store the rank in the table? If you just sort the table by the 'points' column in descending order, then all you need in your template is to output (and increment) a counter variable.

alexcsandru

is verry necesar to store rank in table... coz i used in many way.
For example , if you see detail of player, you can't see what rank is...
example:
Name: alex
Points: 17.300
Rank: (from where to show it?)
Colonization: king's

So i need rank to sore in database table...

I found a solution,

CREATE temporary table seq ( seq int  NOT NULL AUTO_INCREMENT, id int, PRIMARY KEY (`seq`));
INSERT INTO seq ( id, seq )
    SELECT id, NULL FROM UsersTable ORDER BY points DESC;

UPDATE UsersTable INNER join seq ON UsersTable.id = seq.id SET UsersTable.rank = seq.seq;

Basically a temp table using an autonumber key, and inserting into that the id of the user ordered by the points. Then using that to do the update of the rank field in the users table.

So, if someone have something to improve that , many thanks !

bradgrafelman

It'd probably be a lot faster to do something along the lines of your SELECT query above:

SET @rank=0;
UPDATE UsersTable SET rank=@rank:=@rank+1 ORDER BY score DESC;

alexcsandru

Works great, thank you very much.