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

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;
Ph开发者_高级运维otoID = 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.

---

What am i doing wrong?

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

---

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.

---

That you get 30 results if a specific user has 6 photos and 5 comments is quite normal, since you're just fetching the cartesian product of all photos and comments based on user ID. The table structure could shed some light onto possible solutions, but if the comments are related to the photos and you want to fetch all photos and the comments a specific user posted you might use something like :

SELECT * FROM rphotos p
LEFT JOIN comments c on c.photoID = p.photoID
WHERE p.userID = '$defineUserID' OR c.userID = '$defineUserID';

Personally I would split this into 2 queries and display the results separately because mixing them doesn't make any sense to me, ie. use

SELECT * FROM rphotos p
WHERE p.userID = '$defineUserID';

and

SELECT * FROM comments c
WHERE c.userID = '$defineUserID';

edit based on comment

If the ID fields are of the same type you could use something like

select actionID, relatedID, creationDate from
(
select 1 as actionID, photoID as relatedID, creationDate from rphotos
where userID = '$defineUserID'
union
select 2 as actionID, commentID as relatedID, creationDate from comments
where userID = '$defineUserID'
) actions
order by creationDate desc;

The actionID will be 1 for a photo, 2 for a comment and using the relatedID field you could lookup the linked data (if you need it, otherwise you could just drop it from the query).

BTW You probably want to filter the results further (ie. based on date) to prevent joining lots of rows in the union that you won't display...