www.webdeveloper.com
Results 1 to 5 of 5

Thread: Finding a rank in a mysql table

  1. #1
    Join Date
    Nov 2008
    Posts
    2,477

    Finding a rank in a mysql table

    I have 2 tables, 'players' and 'scores'.

    Each player has 1 entry in the players table, and 1 or more entries in the scores table (scores table has player_id as a foreign key).

    I want to find the players rank based on their best score in the scores tables in relations to every other player's best score.

    I currently have some code to do this and it works fine:

    PHP Code:
    $numdays = (isset($this->data['numdays'])) ? $this->data['numdays'] : 0;
    $where '';
    if (
    $numdays) {
        
    $to time();
        
    $from strtotime("$numdays days ago"$to);
        
    $where " AND UNIX_TIMESTAMP(`players`.`created`) BETWEEN $from AND $to";
    }

    $sql 'SELECT MAX(`scores`.`score`) as s
            FROM `scores`
            INNER JOIN `players` ON `players`.`id` = `scores`.`player_id`
            WHERE `players`.`game_finished` = 1 ' 
    $where '
            GROUP BY `scores`.`player_id`
            ORDER BY `s` DESC'
    ;
    if (
    $result mysql_query($sql$this->link)) {
        
    $pos mysql_num_rows($result);
        while (
    $row mysql_fetch_assoc($result)) {
            
    // $score is already set to the score on which to rank the player
            
    if ($score $row['s']) {
                
    $pos--;
            }
        }
        
        echo 
    "$pos\n";

    My question is, this seems like quite an innefficient method, especially as the scores table grows in size. Is there a way of doing this in pure SQL? Or a more efficient PHP method that the one I'm using?

    Thanks
    Last edited by Mindzai; 01-06-2009 at 04:34 AM.

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    you could create a stored procedure.

  3. #3
    Join Date
    Jan 2005
    Location
    Los Angeles, CA
    Posts
    4,887
    Try something like this in phpMyAdmin:
    PHP Code:
    SELECT count(`s`)+as `my rankFROM (SELECT MAX(`scores`.`score`) as s
            FROM 
    `scores`
            
    INNER JOIN `playersON `players`.`id` = `scores`.`player_id`
            
    WHERE `players`.`game_finished` = 1
            GROUP BY 
    `scores`.`player_id`
            
    ORDER BY `sDESC) as `scrWHERE `s` > 450 
    (450 is the $score)

    Getting stylish with chazzy's recommendation, you could first define a custom function in Mysql by running the following query in phpMyAdmin once:
    PHP Code:
    CREATE FUNCTION getscoreranking(myscore int(11),timefrom int(11),timeto int(11))
    RETURNS int(7)
    RETURN (
    SELECT count(`s`)+as `my rankFROM (SELECT MAX(`scores`.`score`) as s
            FROM 
    `scores`
            
    INNER JOIN `playersON `players`.`id` = `scores`.`player_id`
            
    WHERE `players`.`game_finished` = AND UNIX_TIMESTAMP(`players`.`created`) BETWEEN timefrom AND timeto
            GROUP BY 
    `scores`.`player_id`
            
    ORDER BY `sDESC) as `scrWHERE `s` > myscore
    ); 
    Then it will be defined for good until you decide to drop it which is done like so: drop function getscoreranking
    You use getscoreranking like so:
    PHP Code:
    $numdays = (isset($this->data['numdays'])) ? $this->data['numdays'] : 0;
    if (
    $numdays) {
        
    $to time();
        
    $from strtotime("$numdays days ago"$to);
    } else {
        list(
    $from,$to)=array(0,time());
    }
    if(!isset(
    $score))die('$score needs to be set');//a debugging precaution
    $sql="SELECT getscoreranking($score,$from,$to) as rank";
    if(
    $result=mysql_query($sql)){
        
    $row mysql_fetch_assoc($result);
        
    $pos=$row['rank'];
        echo 
    $pos;
    }else{
        echo 
    "Query failed:<br>\n".mysql_error();

    Take note that "SELECT count(`s`)+1 ... WHERE `s` > $score" will return 2 if two people are tried for 2nd place while your algorithm which you posted would return 3 instead and be the equivalent of "SELECT count(`s`) ... WHERE `s` >= $score" if you were to alter my code to your liking.
    Last edited by Ultimater; 01-06-2009 at 11:07 AM.

  4. #4
    Join Date
    Jan 2005
    Location
    Los Angeles, CA
    Posts
    4,887
    An improvement:
    PHP Code:
    CREATE FUNCTION getscoreranking(myscore int(11),timefrom int(11),timeto int(11))
    RETURNS INT(11)
    RETURN (
    SELECT count(`s`)+as `my rankFROM
    (SELECT MAX(`scores`.`score`) as s
            FROM 
    `scores`
            
    INNER JOIN `playersON `players`.`id` = `scores`.`player_id`
            
    WHERE `players`.`game_finished` = AND IF(ISNULL(timefrom) AND ISNULL(timeto),1,UNIX_TIMESTAMP(`players`.`created`) BETWEEN timefrom AND timeto)
            
    GROUP BY `scores`.`player_id`
            
    ORDER BY `sDESC) as `scrWHERE `s` > myscore); 
    It has the additional syntax:
    PHP Code:
    $sql="SELECT getscoreranking($score,NULL,NULL)"

  5. #5
    Join Date
    Nov 2008
    Posts
    2,477
    Thanks for the replies, much appreciated.

    Ultimater your solution works great (and so simple now I've seen it laid out for me!), thanks.

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



Recent Articles