php - MySQL Query to get rank based on the most voted photos of an userGet the 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
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.
Share solution ↓
Link To Answer People are also looking for solutions of the problem: call to a member function format() on string
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.
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.