How to count a column correctly after join 2 tables

Databases
1

Hi,
I have these two tables and the following values


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 MySQL query


SELECT Popular.id
, Popular.user_id
, COUNT(Popular.user_id) as most_popular
, Photo.user_id as photo_uid
, Photo.photo
, Photo.cover_image
FROM Popular
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

and my PHP query with results


echo "->" . $row->most_popular . "<br/>";

-> 8
-> 2

If I don’t join these two tables, the count is accurate.

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.

2

What would the correct result be considering the data you posted?
And what is the relationship between the two tables?

3

Thanks for your help. I resolved this with a foreach loop. It’s take quite a bit long time to figure out though.

4

using a FOREACH loop means you are looping through your query over and over again. This will become significantly slower over time as your table size grows and is almost never the correct solution.

5

Thanks guelphdad,

I’ll keep that in mind, but I haven’t got it done with MySQL query yet. Perhaps I try another method.