In PHP + MySQL, How do I join many tables with conditions

Question

Hi,

I'm trying to get the users full activity throughout the website.

I need to Join many tables throughout the database, with that condition that it is one user.

What I currently have written is:

SELECT * FROM 
comments AS c 
JOIN rphotos AS r 
ON c.userID = r.userID
AND c.userID = '$defineUserID';

But What it is returning is everything about the user, but it repeats rows.

For instance, for one user he has 6 photos and 5 comments So I expect the join to return 11 rows.

Instead it returns 30 results like so:

PhotoID = 1;
CommentID = 1;
PhotoID = 1;
CommentID = 2;
PhotoID = 1;
CommentID = 3;

and so on...

What am i doing wrong?

What I'm trying to achieve (example)

If you're a facebook user, every profile has a 'wall' which states the user's activity on the website in chronological order. I'm trying to make something similar.

Answer 1

You should do it as follows:

SELECT * FROM  user AS u
LEFT JOIN rphotos AS r ON u.userId = r.userID
LEFT JOIN comments AS c ON u.userId = c.userID
WHERE u.userId = '$defineUserID'

Updated to fix silly mistakes

What this does is select all relevant users from the user table (1 in this case) then join in the other tables where necessary and shouldnt repeat rows.

The query also makes more sense when you think about it logically.

Answer 2

If you need get photos with corresponding comments then use this sql query

SELECT * FROM rphotos AS r LEFT JOIN
comments AS c ON c.userID = r.userID where r.userID = '$defineUserID';

one photo has few comments

or if you need get comments with photo (one comment has few photos) then SELECT * FROM comments AS c LEFT JOIN
rphotos AS r ON c.userID = r.userID where c.userID = '$defineUserID';

Answer 3

What am i doing wrong?

you are using one complex query when you could use two simple ones.