CODEWITHSUNDEEP
phpmysqlsqlpdo
Oleg Belousov
Oleg Belousov

Reputation: 10121

Using a variable in MySql to calculate the user's rating on a numeric scalar

On the website that I am currently working on, there is a rating system similar to the one on this site, each client can accumulate 'points' or 'stars'.

Now I am working on an automated emailing system, that will send all of my user's the top 5 list, and their scores, and side by side with the top 5 list, also personal score of the recipient user.

Now since this is routine task on the server(cronjob), the efficiency aspect is highly important, I am using a mySql variable in order to get the rating of each user.

I thought to get the other user and his rating(even if he is on the 100 place) using the same query, but for some reason, I am not getting the correct result.

working code:

    private function getTopStars($adminId){
        try{
            $conn = DBLink::getInstance();
            $query = "SELECT @rownum:=@rownum+1 AS rank, u.user_id, u.contact_name AS name, p.points  FROM scores p, (SELECT @rownum:=0) r, users u WHERE u.stars_enabled = 1 and u.user_id=p.user_id and p.admin_id = :admin_id AND u.user_id <> :adminId ORDER BY points DESC";
            $sth = $conn->prepare($query);
            $sth->bindParam(':adminId', $adminId, PDO::PARAM_INT);
            $sth->execute();
            $topStars = $sth->fetchAll(PDO::FETCH_ASSOC);
            $html = '';
            foreach($topStars as $topStar){
                $html .= " <div class='score'>
                         <div style='float:left;padding:2px;' class='rank'> " .
                        $topStar['rank'] . "# </div><div style='float:left;padding:2px;font-size: 12px;'>" . $topStar["name"] . " </div><div style='float:left;padding:2px;font-size: 11px;'> ( " . $topStar["points"]. " <img src='http://mydev.com/images/star_icon.jpg' /> )</div></div><br style='clear:both;'/>";
            }
            return $html;

        }   
         catch(PDOException $err){...}
}

An addition that refuses to work:

private function getTopStars($adminId, $userId){
        try{
            $conn = DBLink::getInstance();
            $query = "SELECT @rownum:=@rownum+1 AS rank, u.user_id, u.contact_name AS name, p.points  FROM scores p, (SELECT @rownum:=0) r, users u WHERE u.stars_enabled = 1 and (u.user_id=p.user_id and p.admin_id = :admin_id) OR (u.user_id = :uid) AND u.user_id <> :adminId ORDER BY points DESC";
            $sth = $conn->prepare($query);
            $sth->bindParam(':adminId', $adminId, PDO::PARAM_INT);
            $sth->bindParam(':uid', $userId, PDO::PARAM_INT);
            $sth->execute();
            $topStars = $sth->fetchAll(PDO::FETCH_ASSOC);
            $html = '';
            foreach($topStars as $topStar){
                $html .= " <div class='score'>
                         <div style='float:left;padding:2px;' class='rank'> " .
                        $topStar['rank'] . "# </div><div style='float:left;padding:2px;font-size: 12px;'>" . $topStar["name"] . " </div><div style='float:left;padding:2px;font-size: 11px;'> ( " . $topStar["points"]. " <img src='http://mydev.com/images/star_icon.jpg' /> )</div></div><br style='clear:both;'/>";
            }
            return $html;

        }   
         catch(PDOException $err){...}
}

The only thing that I can do if I won't be able to make it work this way, is to implement another private method that will fetch the personal information of each user, using grouping and math sql functions to get his rating in the scores tables, which I consider a waste of server resources.

The logic of the query: I want to receive the 5 users with the top rankings at the moment, ordered and labeled by their place(using the variable) OR the current user to which I am sending the current email, with the same information AND i want to exclude admins from the ranking table. any Idea why won't it work? do I need to index the score column or something.

Thanks in advance for any help!.

Upvotes: 1

Views: 211

Answers (1)

Hazzit
Hazzit

Reputation: 6882

This is the select you describe as "working"

SELECT @rownum:=@rownum+1 AS rank, u.user_id, u.contact_name AS name, p.points  
FROM scores p, (SELECT @rownum:=0) r, users u 
WHERE u.stars_enabled = 1 
  AND u.user_id=p.user_id 
  AND p.admin_id = :admin_id 
  AND u.user_id <> :adminId 
ORDER BY points DESC

This is the select you describe as "not working"

SELECT @rownum:=@rownum+1 AS rank, u.user_id, u.contact_name AS name, p.points  
FROM scores p, (SELECT @rownum:=0) r, users u 
WHERE  u.stars_enabled = 1 
  AND (u.user_id=p.user_id AND p.admin_id = :admin_id) 
  OR  (u.user_id = :uid) 
  AND  u.user_id <> :adminId 
ORDER BY points DESC

In MySQL, "AND" has a higher precedence than "OR", meaning your WHERE condition will be evaluated as:

(u.stars_enabled = 1 AND u.user_id=p.user_id AND p.admin_id = :admin_id)
OR  
(u.user_id = :uid AND  u.user_id <> :adminId )

This is probably not what you wanted, but I don't actually understand what you're trying to achieve.

By your description "I thought to get the other user...." I'm can only take a wild guess as to what you're actually trying to do. The first query (the working one) gives you a list of all users associated with a certain admin_id, so it should include whatever "other user" you are referring to? No?

Edit

I think I now understand your question. The additional user you are talking about is already part of that ranking. What you are trying to do is a way to limit the query to 5 rows plus 1 row for a specific user if that user is not part of the 5 top rows. This can be achieved with a subquery:

SELECT * FROM (
    SELECT @rownum:=@rownum+1 AS rank, u.user_id, u.contact_name AS name, p.points  
    FROM scores p, (SELECT @rownum:=0) r, users u 
    WHERE u.stars_enabled = 1 
      AND u.user_id=p.user_id 
      AND p.admin_id = :admin_id 
      AND u.user_id <> :adminId 
    ORDER BY points DESC
) WHERE rank<=5 or u.user_id=:uid

Upvotes: 1

Related Questions