Ranking list with all the users

Question

I have a database (GAMES) with userid, name, sports and points.

user1, football, 10 points - user1, Basketball, 5 points

user2, footbal, 8 points - user2, Baketball, 3 points

To get the rank of each user by each sports, I am using the following code which is working perfect:

$sql = "SELECT 
sports,
FIND_IN_SET(footbal, (
SELECT GROUP_CONCAT(sports 
ORDER BY points DESC) 
FROM ".GAMES." 
)
) AS rank
FROM ".GAMES."
WHERE userid = 1
";

Results:

user1 (1) (1 is the rank)

When I use user2 in WHERE I get: user2 (2)

Now I want a list like this (For more than 1000 users):

1- User1 (1)

2- User2 (2)

3- User15 (44)

3- ....

Any help will be appreciated. I you need more explanation, just ask.

Hi Harsh, I want a list of all the users with their rank. Like 1- user1 (1) - user9 (88)... — Sara Z, Dec 29 '15 at 09:07
Please clarify if you want "dense" or "gapped" ranking or no respect for ties. If there are 4 users and the "middle middle-ranked" users have the same rank what do you expect? Ranks: `1, 2, 2, 4`, `1, 2, 3, 4`, or `1, 2, 2, 3`? — mickmackusa, Jul 10 '21 at 03:12

score 1 · Answer 1 · answered Dec 29 '15 at 09:12

1

I would do something like this:

$sqls = array();
foreach ($sports as $sport) {
    $sqls[] = "SELECT name FROM ".GAME." WHERE sports='".$sport."' ORDER BY points ASC"
}

Then loop through slqs variable to get all the lists.

And finally, to get the parenthesis part, I would do when I will print the list.

answered Dec 29 '15 at 09:12

jolmos

1,565
13
25

Hi jolmos, thanks for you help. Do I have to add your code to one I already have? – Sara Z Dec 29 '15 at 09:39
Hi, yes, I assumed you have your sports stored in an array($sports), then it's depend on how you fetch and print the results – jolmos Dec 29 '15 at 09:53

ScottSmudger · Answer 2 · 2015-12-29T09:54:57.070

Wouldn't this work?

"SELECT * FROM GAMES WHERE userid = ".$userid." ORDER BY points DESC"

I don't see why you would need to use anything else, as you are just ordering by their points.

or if you also want to specify a sport,

"SELECT * FROM GAMES WHERE userid = ".$userid." AND sports = '".$sport"' ORDER BY points DESC"

You would need to use an array to loop through each sport then just use the above query again. e.g.

$ranks = mysql_query("SELECT * FROM GAMES WHERE userid = ".$userid." AND sports = '".$sport"' ORDER BY points DESC", $database);

$count = 1;
while(list($userid, $name, $sport, $points) = mysql_fetch_row($ranks)) {
    //formatting here. table row, paragraph etc or:
    echo "$count - $name ($userid)";

    $count++;
}

Hi Scott, but you don't get the rank (Position) by each sport. — Sara Z, Dec 29 '15 at 10:15

score 0 · Answer 3 · answered Dec 29 '15 at 09:25

0

You could have 1 select like this:

$sql = 'SELECT * from table_GAMES WHERE points >= 1000 ORDER BY name'

The result would have all users with more than or equal to 1000 sorted alphabetically. You can then display it like this:

$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
  echo $row['name'];
  echo ' (' . $row['points'] . ')';
}

answered Dec 29 '15 at 09:25

stubben

309
2
6

Hi stubben, you misanderstood my question. 1000 means: If I have a lot of users. It has nothing to do with the points. – Sara Z Dec 29 '15 at 09:38

score 0 · Answer 4 · edited May 23 '17 at 11:53

MySQL does not really implement ranking in a convenient way. This is discussed, for example, here: ROW_NUMBER() in MySQL

In that thread linked above you can see some solutions you could try, or alternatively you could use some simpler SQL to get an ordered list and use PHP to calculate/count the ranks:

// ...
$sql = 'SELECT userid, sports, SUM(points) AS total_points FROM games GROUP BY userid, sports ORDER BY sports, SUM(points) DESC';
$result = $mysqli->query($sql);

$rank = null;
$last_sport = null;
$sports_ranking = array();

while($row = $result->fetch_object()) {
    if($row->sports == $last_sport) {
       $rank++;
    } else {
       $rank = 1;
    }
    $sports_ranking[$row->sports][] = array(
        'userid' => $row->userid,
        'rank' => $rank,
        'total_points' => $row->total_points
    );
}

echo('<pre>'); print_r($sports_ranking); echo('</pre>');
// ...

Ranking list with all the users

4 Answers4