2

I am trying to receive a rank from list of scores in a MySQL database and send it to iPhone. I know how to send a value to iPhone but getting a rank from list of scores in a database is little beyond my ability right now.

I have looked into "Get a rank, based on score, from an unordered MySql Database when given a Username" and "MySQL get row position in ORDER BY" and have used the second link's solution to try to come up with my own answer, but it just is not cutting it yet.

In my php file, I receive score, user name (uname), user device id (udid) and using $_POST and enter it into my high score database. After inserting the score, I try to find the user's ranking by looking through scores and finding right 'position' as rank.

My code is:

$rank = 0;
$stmt = $this->db->prepare('SELECT t.id, (SELECT COUNT(*) FROM high_score_4 WHERE score<=t.score) AS position t.score FROM TABLE t WHERE t.score=?');
$stmt->bind_param("i",  $score);
$stmt->execute();
$stmt->bind_result($rank);
while($stmt->fetch()){
     break;
}
$stmt->close();
echo "rank is : " . $rank . "\r\n";

It's the prepare statement that I am quite confused about. I am not even sure if I understood the other post's answer correctly... especially the t.id and TABLE t part

I am quite new to all PHP and MySQL stuff, so my code is most likely quite hectic and wrong fundamentally, but I am sure an experienced eye could probably tell what is wrong with my code immediately, and any help will be greatly appreciated!

Here is my table structure for high_score_4 table:

+----+-------+------------+------------------+
| id | score | uname      | udid             |
+----+-------+------------+------------------+
|1   |  2313 | one        |device-one        |
|2   |  1923 | two        |device-two        |
|3   |  3212 | three      |device-three      |
+----+-------+------------+------------------+

CREATE TABLE high_score_4 (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    score mediumint NOT NULL,
    uname varchar(255) NOT NULL,
    udid varchar(255) NOT NULL,
    puzzles tinyint NOT NULL,
    multiplier tinyint NOT NULL,
    oneshots tinyint NOT NULL,
    hints tinyint NOT NULL,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

so if I look for score of 2313, I want to receive rank 2. I don't need anything else!

Thanks in advance!

Problem solved thanks to Peter's help! Here is the new code:

$stmt = $this->db->prepare('SELECT rank FROM(SELECT id, score, @n := IF(@g = score, @n, @n + 1) rank, @g := score FROM high_score_4, (SELECT @n := 0) i ORDER BY score DESC)q WHERE score=?');
$stmt->bind_param("i",  $score);
$stmt->execute();
$stmt->bind_result($rank);
while($stmt->fetch()){
     break;
}
$stmt->close();
echo "rank is : " . $rank . "\r\n";

and rank is properly printed! Thanks everyone for help!

Community
  • 1
  • 1
nick.jw.park
  • 199
  • 3
  • 12
  • Can you post your table structure. It would make it easier to help you out. Preferably in DDL (create table statement). – Ruben Aug 25 '13 at 08:10
  • Is there any error you are experiencing `SELECT t.id, (SELECT COUNT(*) FROM high_score_4 WHERE score<=t.score) AS position t.score FROM TABLE t WHERE t.score=?` do you need a "," in between `position` and `t.score` frankly I am a bit confused – skv Aug 25 '13 at 08:14
  • @Ruben Thanks for looking into it !I am not sure about DDL but I updated the table structure! there is more columns but they are unnecessary for ranking – nick.jw.park Aug 25 '13 at 08:48
  • @skv I cannot make the errors to show in the terminal.. so I am at a complete loss... (I am really a noob in php...) I don't exactly know what the "," does in there, but in this example [link] (http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by) it has a comma, so I just used it... Thanks for looking at it! – nick.jw.park Aug 25 '13 at 08:51

2 Answers2

6

Try

SELECT id, score, rank
  FROM
(
  SELECT id, score, @n := IF(@g = score, @n, @n + 1) rank, @g := score
    FROM high_score_4, (SELECT @n := 0) i
   ORDER BY score DESC
) q
 -- WHERE score = 1923

Sample output without WHERE:

| ID | SCORE | RANK |
---------------------
|  3 |  3212 |    1 |
|  1 |  2313 |    2 |
|  2 |  1923 |    3 |

Sample output with WHERE score = 1923:

| ID | SCORE | RANK |
---------------------
|  2 |  1923 |    3 |

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
0

I suggest to use the built in RANK function in sql. this orders the scores and puts an extra column with the rank numbers in the sql output. more information can be found here http://technet.microsoft.com/en-us/library/ms189798.aspx

jonas vermeulen
  • 1,235
  • 5
  • 23
  • 40