ketting00 — 2012-10-13T04:44:11-04:00 — #1
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
, 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
and my PHP query with results
echo "->" . $row->most_popular . "<br/>";
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.
guido2004 — 2012-10-13T09:12:03-04:00 — #2
What would the correct result be considering the data you posted?
And what is the relationship between the two tables?
ketting00 — 2012-10-13T10:07:03-04:00 — #3
Thanks for your help. I resolved this with a foreach loop. It's take quite a bit long time to figure out though.
guelphdad — 2012-10-13T11:43:12-04:00 — #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.
ketting00 — 2012-10-14T09:37:18-04:00 — #5
I'll keep that in mind, but I haven't got it done with MySQL query yet. Perhaps I try another method.