How to count a column correctly after join 2 tables
I have these two tables and the following values
and MySQL query
Table Popular Table Photo
id user_id id user_id photo cover_image
1 3 1 1 path_to_photo 0
2 3 2 3 path_to_photo 0
3 1 3 1 path_to_photo 0
4 4 4 1 path_to_photo 0
5 1 path_to_photo 0
and my PHP query with results
, COUNT(Popular.user_id) as most_popular
, Photo.user_id as photo_uid
INNER JOIN Photo
ON Popular.user_id = Photo.user_id
WHERE Popular.user_id != '$user_id'
GROUP BY Popular.user_id
ORDER BY most_popular DESC
If I don't join these two tables, the count is accurate.
echo "->" . $row->most_popular . "<br/>";
I need the cover_image column too becuase I would make it display the user selected picture. In this case, both of the users don't pick any image as cover image yet.