SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update rank field

    How can I update the rank column in the below table for;

    1. just one category? This is to be done when a user wants to see the rankings for a specific category. In this case the rankings for the user himself, the top 10 users and the user's selected friends will be shown together.

    2. for all categories individually? This is to be done when a user wants to see his own rankings in all categories.

    There will be around 100 categories and the number of users is around 10 000 and growing.

    category ; username ; score ; rank
    run ; bill ; 10 ; 1
    run ; joe ; 7 ; 2
    run ; sue ; 4 ; 3
    jump ; sue ; 12 ; 1
    jump ; bill ; 6 ; 2

  2. #2
    SitePoint Member
    Join Date
    Mar 2011
    Location
    Tyne & Wear
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How regular does an update happen, as if the rank is constantly changing you might have to use another method that doesnt require re-calculating the whole list.


    Something LIKE this maybe your answer if you are updating one category at a time
    PHP Code:
    SET @rowCount := 0;
    SELECT categoryusernamescore, (@rowCount :=@rowCount 1) AS rank 
       from RankTable where category 
    '%%CATEGORY%%' ORDER BY score DESC 
    You can use this live, or use it to update this or another table to cache your ranks.

    Hope this helps in some way, its the only easy and fast way I can think of at this moment in time.

  3. #3
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much for your reply!

    Rank was included in the result using your code and when I was looking for a way to update the rank column in the original table I found this variant that does this and also checks for ties.

    Code:
    UPDATE   MyTable 
    JOIN     (SELECT    p.username,
                         IF(@lastScore <> p.score,
                             @curRank := @curRank + 1,
                             @curRank)  AS rank,
                         IF(@lastScore = p.score,
                             @curRank := @curRank + 1,
                             @curRank),
                         @lastScore := p.score
               FROM      MyTable p
               JOIN      (SELECT @curRank := 0, @lastScore := 0) r
               ORDER BY  p.score DESC
              ) ranks ON (ranks.username = MyTable.username)
    SET      MyTable.rank = ranks.rank;
    This works fine if I have a table without different categories. But if I add the category column and limit the select to one category I get an '#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY p.score DESC ) ranks ON (ranks.id = MyTable.id) SET '. (I also used the id column here instead of the username column since there are multiple rows with the same username).

    Code:
    UPDATE   MyTable 
    JOIN     (SELECT    p.id,
                         IF(@lastScore <> p.score,
                             @curRank := @curRank + 1,
                             @curRank)  AS rank,
                         IF(@lastScore = p.score,
                             @curRank := @curRank + 1,
                             @curRank),
                         @lastScore := p.score
               FROM      MyTable p WHERE category = 'run'
               JOIN      (SELECT @curRank := 0, @lastScore := 0) r
               ORDER BY  p.score DESC
              ) ranks ON (ranks.id = MyTable.id)
    SET      MyTable.rank = ranks.rank;
    The code specified in the error message seems to be the same so any ideas why I get this error message?

    This update will happen whenever a user wants to see a highscore list but the user needs to click a link manually for this. When can updates like this start causing problems. When it's 10 every minute or when it's 10 000 every minute? The update will sometimes be done for all categories individually and sometimes for just one category. I have a regular web hosting account.

    Many thanks

  4. #4
    SitePoint Member
    Join Date
    Mar 2011
    Location
    Tyne & Wear
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where was in the wrong place
    PHP Code:

    UPDATE   MyTable 
    JOIN     
    (SELECT    p.id,
                         IF(@
    lastScore <> p.score,
                             @
    curRank := @curRank 1,
                             @
    curRank)  AS rank,
                         IF(@
    lastScore p.score,
                             @
    curRank := @curRank 1,
                             @
    curRank),
                         @
    lastScore := p.score
               FROM      MyTable p
               JOIN      
    (SELECT @curRank := 0, @lastScore := 0r
               WHERE category 
    'run'
               
    ORDER BY  p.score DESC
              
    ranks ON (ranks.id MyTable.id)
    SET      MyTable.rank ranks.rank

  5. #5
    SitePoint Member
    Join Date
    Mar 2011
    Location
    Tyne & Wear
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also for adding abilty to show movement you can store history of the previous rank

    PHP Code:
    UPDATE   MyTable 

    JOIN     
    (SELECT    p.id,

                         IF(@
    lastScore <> p.score,

                             @
    curRank := @curRank 1,

                             @
    curRank)  AS rank,

                         IF(@
    lastScore p.score,

                             @
    curRank := @curRank 1,

                             @
    curRank),

                         @
    lastScore := p.score

               FROM      MyTable p

               JOIN      
    (SELECT @curRank := 0, @lastScore := 0r

               WHERE category 
    'run'

               
    ORDER BY  p.score DESC

              
    ranks ON (ranks.id MyTable.id)

    SET      MyTable.lastRank MyTable.rankMyTable.rank ranks.rank
    When selecting from this table (rank - lastRank) will indicate movement
    < 0 Moved up in rank
    == 0 Same Position
    > 0 Moved down in rank.

    Plus only update the rank table when the a users score is updated, which happens alot less than viewing scores.

  6. #6
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much for correcting WHERE! As you've noticed I'm pretty new at this. Now it seems to be working fine. Do you know if I need to run a PHP loop if I want all categories to be ranked?

    Thank you also for the lastRank addition. I can be a nice addition to the list and I will try to add it.

  7. #7
    SitePoint Member
    Join Date
    Mar 2011
    Location
    Tyne & Wear
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the score for?
    Does it affect one category at a time ?

    If it does, you only need to update that category once an update to a score is done, meaning when a user looks up there score, its a simple row fetch and now heavy sql processing.

  8. #8
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's an online quiz and I think the score will be updated more often than the highscore is viewed. But since updating the rankings for one single category seems to be a fast query it's probably better to, as you say, do this every time a score changes rather than updating the rankings for all categories when someone wants to view the scores.

    I will continue and implement this. Thank you again for your kind help with this. I was trying many different solutions but as a beginner it is easy to get stuck when adapting schoolbook examples to 'real life'.

  9. #9
    SitePoint Member
    Join Date
    Mar 2011
    Location
    Tyne & Wear
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Np once you finish it, post the link in here so we can see it running


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •