php - MySQL Query to get rank based on the most voted photos of an user

Solution:

If you are looking for a specific photo, then ignore grouping by the person, but the specific element, THEN roll-back that information to get the user. Your use of sqlvars @rownum principle will remain the same.

Have your inner query be more like (guessing on column name of specific "Photo_ID" would be in the table also)

select
       @rownum := @rownum +1 as rank,
       prequery.user_id,
       prequery.photo_ID,
       prequery.vote_count
   from
      ( select 
              v.User_ID,
              v.Photo_ID, 
              count(*) as Vote_Count
           from 
              IsItStillTheVotesTable v
           group by 
              v.User_ID,
              v.Photo_ID
           order by 
              count(*) desc ) as prequery,
      ( select @rownum := 0 ) sqlvars
   HAVING
      prequery.user_id = '$me->id'
   LIMIT 
      0,1
Copy code

The "prequery" will already return the records in proper descending order ranked sequence. Then, you have to apply the @rownum to all rows possible and apply a HAVING clause. The Where clause typically pre-checks if a record qualified for the result set and throws out if not before the @rownum would get assigned. The HAVING clause allows the record to have the @rownum get applied, THEN look at it for your "user_ID" and if not, throws it out. This way, you'll get your rank on the specific combination of user AND specific photo.

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.