Get user position in highscore, PDO

[nkls]

Hi! I´m learning PDO at the moment, but struggling a bit.. I want to get a specific users position in my highscore. I would lite the output to be like "your position is 14th of 200. I have tried to search, but i cant get it to work at all :/

Im using an uniqe string as udid, and want to get the rank of that udid based on score...

the connection works but something is wrong in my sql statement..

try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }

    $udid = $_GET['udid'];
    

    function getUserRanks($udid, $conn) {
    $sql =  "SELECT COUNT(udid) AS `rank` FROM myTable WHERE score > ( SELECT score from myTable WHERE udid = $udid )";

    $stmt = $conn->prepare($sql);
    $stmt->bindValue(1, $udid);
    $stmt->execute();
    $ranks = $stmt->fetchObject();
    return $ranks;
}
    
$ranks = getUserRanks($udid, $conn);     
echo $ranks;
?>

im getting this error:

"Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'myLongUdidStringHere'"

 

Would be very happy if someone could help me with my function or point me in the right way! Thanks a lot!

 

 

[Strider64]

        $query = "SELECT count(1) FROM myTable WHERE score > :user_score";
        $stmt = $pdo->prepare($query);

        $stmt->execute([':user_score' => $user_score]);
        $ranks = $stmt->fetchColumn();

Why not simply do something like the above? (Pull the score separately from the query is what I would do) 

 

That might have to be >= rather than > ? 

Edited February 17, 2017 by Strider64

[Barand]

You shouldn't be putting $userid in the query string. It looks as though the actual query being executed is

SELECT COUNT(udid) AS `rank` FROM myTable WHERE score > ( SELECT score from myTable WHERE udid = myLongUdidStringHere

Because "myLongUdidStringHere" isn't in quotes, sql treats it as a column name. Try

function getUserRanks($udid, $conn) {
    $sql =  "SELECT COUNT(udid) AS `rank` 
            FROM myTable WHERE score > 
                ( SELECT score from myTable WHERE udid = :udid )";

    $stmt = $conn->prepare($sql);
    $stmt->execute( [ ':udid' => $udid ] );
    $ranks = $stmt->fetchObject();
    return $ranks->rank;
}

[nkls]

 

You shouldn't be putting $userid in the query string. It looks as though the actual query being executed is

SELECT COUNT(udid) AS `rank` FROM myTable WHERE score > ( SELECT score from myTable WHERE udid = myLongUdidStringHere

Because "myLongUdidStringHere" isn't in quotes, sql treats it as a column name. Try

function getUserRanks($udid, $conn) {
    $sql =  "SELECT COUNT(udid) AS `rank` 
            FROM myTable WHERE score > 
                ( SELECT score from myTable WHERE udid = :udid )";

    $stmt = $conn->prepare($sql);
    $stmt->execute( [ ':udid' => $udid ] );
    $ranks = $stmt->fetchObject();
    return $ranks->rank;
}

wow, I love Internet! Works great, thanks!

May I ask you one more thing? This gives me my position in the table based on the id-sorting. If i want to sort my table on score before doing this, where would i put my ORDER BY score? Sorry, totally new on this..

[Barand]

The only query you have posted will only return a single result with a count in it, so sorting one record does not make sense.

 

What are you trying to achieve?

[nkls]

The only query you have posted will only return a single result with a count in it, so sorting one record does not make sense.

 

What are you trying to achieve?

hmm, ok. I just want the position of the user i pass in (by udid) based on his/her score..

ideally i would like the output of my echo to be something like this:

14th of 200

 

where 14 is user position based on score and 200 is total scores..

 

[Barand]

You can do something like this

 

my data

mysql> select * from score;
+-------+-------+
| name  | score |
+-------+-------+
| Bob   |    75 |
| David |   106 |
| Jane  |    75 |
| Joe   |    61 |
| Mary  |    59 |
| Mike  |    61 |
| Sam   |    76 |
+-------+-------+
7 rows in set (0.00 sec)

query

SELECT a.name 
  , a.score
  , COUNT(b.name)+1 as rank
  , tot
FROM score a
  JOIN (SELECT COUNT(name) as tot FROM score) as total
  LEFT JOIN score b ON b.score > a.score
WHERE a.name = 'Jane'                                  -- OPTIONAL
GROUP BY a.name
ORDER BY a.score DESC;

+------+-------+------+-----+
| name | score | rank | tot |
+------+-------+------+-----+
| Jane |    75 |    3 |   7 |
+------+-------+------+-----+

Edited February 17, 2017 by Barand

[nkls]

thanks for helping me berand, but I still cant get it to work. I feel a bit stupid, hope you can bare with me.

 

my udid comes from a $udid = $_GET['udid'];, and that seems to work.

My table is namned myTable, and my version of your "name" is udid. the score is called score..

would this be correct?:

 $sql =  "SELECT a.udid
  , a.score
  , COUNT(b.udid)+1 as rank
  , tot
FROM myTable a
  JOIN (SELECT COUNT(udid) as tot FROM myTable) as total
  LEFT JOIN myTable b ON b.score > a.score
WHERE a.udid = $udid                                
GROUP BY a.udid
ORDER BY a.score DESC;";

I really appreciate your help!

Edited February 17, 2017 by nkls

[Barand]

Have you tried it without the WHERE line? That will give all scores with ranks.

 

Is $udid numeric? If not, it needs to be inside single quotes. (Really it shouldn't be in the query at all, it should be a prepared query with a placeholder for the udid parameter. But that's another lesson.

[nkls]

got it all sorted out =) thanks a lot!