Leaderboard, with position, dealing with same points and skipping to next

Question

I have some code where I calculate the users points and position on the leaderboard then display them in descending order based on their points/score.

At the moment I have done the numbering so that it looks like this

1
2
2
3
3
3
4

However I wanted to make this more correct and skip positions based on the number before

So it needs to look like this

1
2
2
4
4
4
7

My SQL Query is as follows:

$query = $db->query("select a.user_id, b.username, sum(a.points) as points, c.paid, a.time_entered
from ".TABLE_PREFIX."mytipper_tips a
inner join ".TABLE_PREFIX."users b on a.user_id = b.uid
Inner join ".TABLE_PREFIX."mytipper_users c on b.uid = c.uid
where c.compID=".intval($comp)." and a.compID=".intval($comp)."
group by a.user_id order by points desc, username asc");`

and then I loop through them with the following code:

//now we have the query we can iterate through the list of users

$position = 1;
while($result=$db->fetch_array($query)) {

    //check if it is a paid comp and if the user has paid, if so we only want to do this for the paid userds

    if($is_paid==1 && $result["paid"]==1)   {
        $display = "1";

    } else if($is_paid==1 && $result["paid"]!=1)    {
        $display = "0";
    } else if($is_paid==0)  {
        $display = "1";
    }

    if($display=="1")   {
        //set the table row for display
        if($row==2 || $row="")  {
            $row=1;
        }   else    {
            $row=2;
        }
        $username = htmlspecialchars_uni($result['username']);
        $user_id = intval($result["user_id"]);
        if($points==$result["points"])  {
            $position--;
        }
        $points = intval(($result["points"]));

        $leaderboard_link = mytipper_check_build_sef("misc.php?id=".intval($comp)."&user=".intval($user_id)."&mytipper=leaderboard_detail", $title);

        if($margin_leaderboard=="1")    {
            $margin = "(".htmlspecialchars_uni($result["actual_result"]).")";
        }   else {
            $margin="";
        }
        eval("\$leaderboard_rows .= \"".$templates->get("leaderboard_row")."\";");
        $position ++;
    }
}`

I can't get my head around correcting those numbers

The way I am doing it now is I take the position and I subtract one to keep it on the same number as the previous score.

But how do I then make the leap from say number 2 to number 4?

If this could be done as part of the query even I would be happy

Read this post : http://dba.stackexchange.com/a/41483 . You've got the answer inside. ;) I won't give you a better answer because I'm not sure I can without a SQLfiddle. — kmas, Mar 11 '14 at 15:04

score 2 · Accepted Answer · answered Mar 11 '14 at 15:09

Maybe with a simple counter + storing last position and score ?

<?php
    $i         = 1; // Item counter
    $lastScore = 0; // Last score
    $lastPos   = 0; // Last position

    foreach( $... ) {

       $myPosition = $i; // My position equals item counter
       // If last score equals this score, my position equals last position
       if( $lastScore > 0 && $myscore == $lastScore ) {
           $myPosition = $lastPos;
       }
       $lastScore = $myScore;
       $lastPos   = $myPosition 

        ++$i;
    }

score 0 · Answer 2 · answered Mar 11 '14 at 15:09

0

Conceptually it sounds like you should approach the situation by keeping a count of the number of ties so that when you do the loop it should work:

int currentPositionToDisplay = 0;
int positionsToMoveToNext = 1;
int currentPositionScore = MAX_INT;
While(person in row)
{
    if(person.score < currentPositionScore)
    {
        currentPosition += positionsToMoveToNext;
        currentPositionScore = person.score;
        positionsToMoveToNext = 1;
    }else
    {
       positionsToMoveToNext++;
    }
    PRINT currentPositionToDisplay;
}

This allows you to keep a count of the number of items you skipped then add those when moving to the next.

answered Mar 11 '14 at 15:09

RacerNerd

1,579
1
13
31

This was my thought but since it's a loop, how do I reset that counter to work. – Dannymh Mar 11 '14 at 15:24
Th resetting of the counter should happen in the line that says "positionsToMoveToNext = 1". Once it detects a difference the counter is used and reset. – RacerNerd Mar 11 '14 at 17:22

Leaderboard, with position, dealing with same points and skipping to next

2 Answers2