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

Answer 1

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;
    }