CODEWITHSUNDEEP
phpmysql
DT.DTDG
DT.DTDG

Reputation: 765

How to check if player is in the top 10 scores (their ranking)

I have players whose scores are added to the tabled and I'd like to do a check in PHP to see whether or not their score is within the top 10 or outside of the top 10.

I have the following in place and now need to add the query to sort highscore in DESC and to let me know if that userid is ranked in the top 10 or not.

Current:

    $currentuser = $_SESSION['userid'];
    $mysqli = new mysqli($hostname, $db_username, $db_password,$db_name);
    if ($mysqli->connect_error) {
        die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }   

    $res = $mysqli->query("SELECT * FROM players WHERE userid=" . $currentuser);

Attempt:

    $currentuser = $_SESSION['userid'];
    $mysqli = new mysqli($hostname, $db_username, $db_password,$db_name);
    if ($mysqli->connect_error) {
        die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }   

    $current = $mysqli->query("SELECT * FROM players WHERE userid=" . $currentuser);
    $res = $mysqli->query("SELECT * FROM players ORDER BY highscore DESC LIMIT 10");

    foreach ($res as $player) {
       if ($player = $current) {
         echo "in the top 10" }
       else {
         echo "not in the top 10"} 
    }

Is this correct and is it the correct logic to check if $currentuser is in the top 10 or can it be achieve in a cleaner way?

Upvotes: 0

Views: 230

Answers (2)

Ormoz
Ormoz

Reputation: 3013

you can use the following query: 

 $query="SELECT 1 from players WHERE  $currentuser in (SELECT userid FROM players ORDER BY highscore DESC LIMIT 10) LIMIT 1"
if($result=$mysqli->query($query))
{
if($result->num_rows >0 )echo 'in top 10';
else echo 'not in top 10';
}
else echo 'error';

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

Assuming you want to list all users, and then denote which are in the top 10, then here's one option combining the entire list into a single query using a subquery to get the top 10 players, a case statement and an outer join:

select p.userid, 
  case 
    when p2.userid is not null 
    then 'in the top 10'
    else 'not in the top 10'
  end intopten
from players p
  left join (select *
        from players
        order by highscore desc
        limit 10) p2 on p.userid = p2.userid

Given your comments, this might be the easiest option:

select *
from (
  select *
  from players
  order by highscore desc
  limit 10
) t
where userid = 1

Upvotes: 1

Related Questions