Hi,
I have 2 tables: users (id, name) and scores (id, user_id, point).
Users table stores user information and scores table stores points attached to the user on user_id. One user can have more points. I’d like to make a 1) high score rank list and 2) method which returns a placement to a user.
I have some difficulties with 2). I can make a list of high scores via the following query:
SELECT u.name, u.id, SUM(s.score) as scoresSum FROM tbl_users as u INNER JOIN tbl_scores as s ON s.user_id=u.id GROUP BY u.name ORDER BY scoresSum DESC
This one gives the correct list of user ranking but if I would like to show the user’s current ranking on the user profile page it doesn’t work. I found a formula to compute the ranking for a user but the GROUP BY messes up the query somehow and gives back a wrong result.
So I added a new column to the users table: ‘scoresum’ which stores the sum of the scores of a user.
But here I have another error. Maybe it isn’t allowed to put such a queries in a single method:
The queries:
SET @row = 0;
SELECT rank, id FROM (SELECT @row := @row + 1 AS rank, name, id, scoresum FROM tbl_users ORDER BY scoresum DESC) as q WHERE id=’:id’;
The yii methods:
$sql = "SET @row = 0; SELECT rank, id FROM (SELECT @row := @row + 1 AS rank, name, id, scoresum FROM tbl_users ORDER BY scoresum DESC) as q WHERE id=':id';";
$command = $connection->createCommand($sql);
$command->bindValue(":id", $this->id, PDO::PARAM_INT);
$dataReader = $command->query();
foreach($dataReader as $row) {
var_dump($row);
}
I get this error: SQLSTATE[HY000]: General error
Could someone give me hints how I can solve this ‘getting ranking to a user’ problem?
Thank you in advance!