Results 1 to 5 of 5

Thread: rankings: 5th place out of 143 - 1st out of 475 etc

  1. #1
    Join Date
    Mar 2010

    rankings: 5th place out of 143 - 1st out of 475 etc

    i want a way to rank users.

    it will be based on a 'score' which is accrued, i want to be able to:

    tell the user what place they're in out of the number of users on the site

    for example:

    "doofmeat you are 5th out of 375 with 9321 points"

    problem is, i'm horrific at maths, and wouldn't know what sums to use.

    to put it into perspective: it took me a week to figure out how to make a poll.

    i know i could possibly just grab someone else's script and rag it about to meet my needs but i'd rather know it, ya know?

    any help greatly appreciated!

  2. #2
    Join Date
    Mar 2010
    nobody any ideas?

  3. #3
    Join Date
    Mar 2010
    Something such as, if you are using mysql.

    Grab a Query with "WHERE score>=user_score ORDER BY score"

    Then mysql_num_rows to grab the position the user is in.

    Hope this helps.

  4. #4
    Join Date
    Jul 2010
    I would say handle the position they're in using mysql. The reason is because it's about 5-6 more words compared to a lot of php code.

    If it's multiple entries (lets say a score tied to each event) then you'll need to use 'GROUP BY' and 'SUM(score)' in mysql to get the total score, or COUNT(user) AND SUM(score) to get the number of users ahead of the one in question.

    Lets say you want to find the the user in 5th place.

    SELECT user, sum(score) as score 
    FROM score_table
    GROUP BY user
    ORDER BY score DESC
    LIMIT 4,1
    SUM(score) is an aggregate function (who knows what that means... lol). It requires the table be grouped because it's function is to sum the values in a column which get collapsed based on the GROUP BY function which groups the rows based on another column.
    So the offset in "LIMIT" is 4, and this query selects 1 row the next row (5th row). This works because the query orders the rows in descending order.

    Now lets say you're trying to tell each user their rank compared to other users.

    "SELECT COUNT(user) as place, SUM(score) as sum_score FROM score_table
    WHERE sum_score>( 
           SELECT SUM(score) as comp_score 
           FROM score_table as ST 
           WHERE ST.user=".$_SESSION['user']." 
           GROUP BY user
    GROUP BY user";
    The order in this table doesn't matter because you can COUNT the number of users in any order and get the same number, but it still groups the table. This time score of the user in question is being compared to the other scores using a subquery.

    Hope it helps.

  5. #5
    Join Date
    Mar 2010
    that's brilliant lads thanks for your help i'll try these when i get home.

    nothing to Group By though so i'll be leaving that out.

    it's out of all users.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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