Data from MySQL – If users have the same number of points – show in one row

How can I make if player X has the same number of points as any other player, so that it displays them in 1 row?

table users:

`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(120) NOT NULL DEFAULT '',
`points_first` int(11) NOT NULL DEFAULT 0,
`points_second` int(11) NOT NULL DEFAULT 0,

My Query:

SELECT uid, username, points_first, points_second
FROM users
LIMIT 5

Results:

PHP to sort data DESC and display using templates:

$query = $db->query('SELECT uid, username, points_first, points_second FROM users');
        $no = $noFirst = $noSecond = 0;
        $dataFirstPoints = $dataSecondPoints = [];
        $i = 0;
        while($row = $db->fetch_array($query)){
            if($row['points_first'] != 0){
                $dataFirstPoints[$i]['points'] = $row['points_first'];
                $dataFirstPoints[$i]['username'] = $row['username'];
            }
            if($row['points_second'] != 0){
                $dataSecondPoints[$i]['points'] = $row['points_second'];
                $dataSecondPoints[$i]['username'] = $row['username'];
            }
            $i++;
            if($row['points_second'] === 0) $firstRows = "<div class="nodata">No Data</div>";
            if($row['points_first'] === 0) $secondRows = "<div class="nodata">No Data</div>";
        }
        arsort($dataFirstPoints);
        arsort($dataSecondPoints);
        foreach($dataFirstPoints as $data){
            $points = $data['points'];
            $noFirst++;
            $no = $noFirst;
            eval("$secondRows .= "".$templates->get("rows")."";");
        }
        foreach($dataSecondPoints as $key => $data){
            $points = $data['points'];
            $noSecond++;
            $no = $noSecond;
            eval("$firstRows .= "".$templates->get("rows")."";");
        }
        eval("$main = "".$templates->get("main")."";");

Results from PHP and templates:

Now we can see that two users have points in the same category (points_first). How can I do to get this effect? What to use?:

Answer

Group the results by the points and use GROUP_CONCAT to concatenate the usernames.

SELECT points_first, GROUP_CONCAT(username SEPARATOR ', ') AS users FROM users GROUP BY points_first ORDER BY points_first DESC;

I hope it is helpful!

Advertisement

Answer