0

I'm trying to build a membersystem with a leaderboard that ranks users based on the number of hearts their posts receives. Up to the leaderboard itself everything works fine. Since I'm new to mysqli I googled around for a way to fetch the rank of a user, without building the whole leaderboard every single time. I found this thread: MySQL Get Rank from Leaderboards, which uses this solution: 

SELECT  uo.*,
    (
    SELECT  COUNT(*)
    FROM    Scores ui
    WHERE   (ui.score, -ui.ts) >= (uo.score, -uo.ts)
    ) AS rank
FROM    Scores uo
WHERE   name = '$name';

This seems to make sense, and I understand structure, but I can't seem to figure out how to put this in a statement that actually works. I try to use prepared statements normally, but I'm not sure if that works with this syntax (I couldn't make it work). Eventually I ended up turning around the whole thing into the following code, resulting in the display of "$count_heart", which is 0, and "no good". 

if ($sql = $db->prepare("SELECT musichearts FROM members WHERE id = ?")) {
    $sql->bind_param('i',$id);
    $sql->execute();
    $sql->bind_result($count_heart);
    while ($sql->fetch()) {
        if ($sql2 = $db->prepare("SELECT COUNT(*) FROM members WHERE (musichears > ?)")) {
            $sql2->bind_param('i',$count_heart);
            $sql2->execute();
            $col1 = null;
            $sql2->bind_result($hearts_musi);
            while ($sql2->fetch()) {
                echo "counted {$col1} records\n";   
            }
            $sql2->close();
        } else {
            echo "no good";   
        }
    }
    echo $count_heart;
    echo $hearts_musi;
}

Any help would be much appreciated!

Community
  • 1
  • 1
Rob Teeuwen
  • 455
  • 5
  • 21
  • What does this mean: `WHERE (ui.score, -ui.ts) >= (uo.score, -uo.ts)`? – Peter Bowers Mar 11 '15 at 07:32
  • it's quite well explained in the link where i got it from (http://stackoverflow.com/questions/27231364/mysql-get-rank-from-leaderboards), but as far as I understand things the inner query counts the number of rows that have a score which is greater than the score in the row that is fetched by the outer query. ts is another column in the table which is for some reason subtracted (i deleted this when I tried to use it on my code). – Rob Teeuwen Mar 11 '15 at 07:37
  • Huh - never saw that before. Apparently it's called a tuple. Thanks for teaching me something today! – Peter Bowers Mar 11 '15 at 08:22

0 Answers0