0

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.

Moe
  • 1,469
  • 3
  • 11
  • 9

3 Answers3

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.

Chris
  • 26,744
  • 48
  • 193
  • 345
  • Your where is on the wrong place, but apart from that you should try this query as is in MySQL and then be amazed when it returns just a single row because you only group by userID. That's probably not what you'd expect heh... – wimvds Apr 29 '10 at 08:26
  • OP never asked about listing all users - outer joins are expensive. – symcbean Apr 29 '10 at 11:28
1

What am i doing wrong?

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

o0'.
  • 11,739
  • 19
  • 60
  • 87
  • True, But how Would I group them and order them in chronological order? Like Facebook's 'Wall' – Moe Apr 29 '10 at 08:25
  • How would I go about doing that? – Moe Apr 29 '10 at 11:08
  • As per comment on wimvds reply - it might be 2 bits of SQL but its lots and lots of queries. – symcbean Apr 29 '10 at 11:29
  • ...but judging from the OP's reply elsewhere, the original question asked is very misleading and maybe it is just 2 executions. – symcbean Apr 29 '10 at 11:32
  • @Moe: how? UNIX_TIMESTAMP the date and then aasort or array_multisort http://stackoverflow.com/questions/2699086/php-sort-multidimensional-array-by-value/2699110#2699110 – o0'. Apr 29 '10 at 14:56
1

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...

wimvds
  • 12,790
  • 2
  • 41
  • 42
  • I would like to put them in chronological order and display the user actives from most recent to lest. – Moe Apr 29 '10 at 08:29
  • I just edited my response that could help in that case. You can add other fields too, as long as the data of the 2 queries in the union is similar. – wimvds Apr 29 '10 at 08:52
  • "Personally I would split this into 2 queries and display the results separately" - but you're not splitting them into 2 queries - you need to rerun the comments query for each photo row. Assuming that the photos table doesn't contain the actual image data (then, due to the size of the returned data set this is the most efficient approach) its probably a lot more efficient to do a single query with an outer join, ordered by something from the photo table, and only output photo data when it changes from one row to the next. – symcbean Apr 29 '10 at 11:27
  • First keep in mind the original question was changed :p. The first scenario is the one where he wants to retrieve all data in one go, assuming (!) photos and comments were linked (TS never said they were, and since he's not providing any info on the table structure we have to guess). This is one that - to me - doesn't make any sense anyway. The second scenario (separate queries) just fetches the photos and comments for a specific user, and nothing more. So long as he doesn't provide any more information (ie. table structure), it's as good as it gets. – wimvds Apr 29 '10 at 11:50