mattjon mattjon - 5 months ago 7
SQL Question

Get one rows number from SQL query

I'm tryin to figure out how to get one rows number from a SQL table. In my case I need to get the rank for a user based on his/hers score. I have the code below that will do it but is there a better way to accomplice this in just a query?

$rank = 0;
$username = $_SESSION['user'];

$query = "SELECT username, SUM(`score`) as total_score FROM answers GROUP BY username ORDER BY total_score DESC";
if ($stmt = mysqli_prepare($connect, $query)) {
mysqli_stmt_bind_param($stmt);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $uname, $score);
while (mysqli_stmt_fetch($stmt)) {
$user = $uname;
$rank++;
if ($user == $username) {
echo $rank;
break;
}
}
mysqli_stmt_close($stmt);
}

Answer

you can get rank from your sql

SELECT username, SUM(`score`) as total_score,@curRank := @curRank + 1 AS rank FROM answers,(SELECT @curRank := 0) GROUP BY username ORDER BY total_score DESC


example 

select DONATUR,COUNT(DISTINCT AREA) ,@curRank := @curRank + 1 AS rank from funding,(SELECT @curRank := 0) r group by Donatur;
+---------+----------------------+------+
| DONATUR | COUNT(DISTINCT AREA) | rank |
+---------+----------------------+------+
| Mr.X    |                    3 |    1 |
| Mr.Y    |                    1 |    2 |
| Mr.Z    |                    2 |    3 |
+---------+----------------------+------+
3 rows in set (0.00 sec)

if you want to get only one user in the middle of the list you can do like this

mysql> select DONATUR,COUNT(DISTINCT AREA) ,@curRank := @curRank + 1 AS rank from funding,(SELECT @curRank := 0) r group by Donatur having DONATUR="Mr.Y";
+---------+----------------------+------+
| DONATUR | COUNT(DISTINCT AREA) | rank |
+---------+----------------------+------+
| Mr.Y    |                    1 |    2 |
+---------+----------------------+------+
1 row in set (0.00 sec)

use this query

SELECT @curRank := @curRank + 1 AS rank,username,score from  (select username,COUNT(DISTINCT score) AS score  from answers group by username  order by COUNT(DISTINCT score) ) y,(SELECT @curRank := 0) r where username=?";