CODEWITHSUNDEEP
phpmysql
mattjon
mattjon

Reputation: 29

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

Upvotes: 0

Views: 79

Answers (1)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 3008

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=?";

Upvotes: 1

Related Questions