Recent User Actions - PHP MySql

Question

Here is my problem: I have 3 MySql tables representing: photos, user posts, user videos, user comments, and I need to view the last 10 (20, 30, 40 ...) users. For example, in the table of photographs can be compiled:

  user_id |  photo_id |  photo_path |  photo_name |  date_added
    5 |  18 |  / photos |  pht_18.png |  2009-02-12
    5 |  21 |  / photos |  pht_21.png |  2009-02-15
    5 |  29 |  / photos |  pht_29.png |  2009-03-30 

video table

  user_id |  video_id |  video_url |  date_added
    5 |  36 |  youtube.com / ... |  2009-01-09
    5 |  48 |  youtube.com / ... |  2009-02-18
    5 |  90 |  youtube.com / ... |  2009-03-19

comment table

  user_id |  comment_id |  comment |  date_added
    5 |  6 |  hi!  |  2009-02-11
    5 |  11 |  great photo |  2009-02-13
    5 |  19 |  nice shot!  |  2009-03-28

As you can see, 3 tables have a different number of attributes, so how can I do a join? and when retrieving the result of the query, how can I understand which table it belongs to?

So, on the user profile page, I would like to show his recent actions, which, of course, are ordered by DATE DESC as follows:

  2009-09-01: user posted a video
 2009-11-02: user posted a comment
 2009-12-02: user posted a photo
 2009-13-02: user posted a comment
 2009-15-02: user posted a photo
 2009-18-02: user posted a video
 2009-19-03: user posted a video
 2009-28-03: user posted a comment
 2009-30-03: user posted a photo

Can anybody help me?

Answer 1

The MySQL UNION query may work here:

(SELECT `user_id`, `date_added`, 'photo' AS `type` FROM `photos` WHERE `user_id` = uid) UNION (SELECT `user_id`, `date_added`, 'video' AS `type` FROM `videos` WHERE `user_id` = uid) UNION (SELECT `user_id`, `date_added`, 'comment' AS `type` FROM `comments` WHERE `user_id` = uid) ORDER BY `date_added` DESC; 

Then you end up with a result similar to

 user_id | date_added | type 5 | 2009-01-03 | photo 5 | 2008-12-07 | video 5 | 2008-11-19 | comment 

etc. (in fact, you can leave user_id outside SELECT if you want, of course)

Answer 2

Why do you have separate tables in the first place? This is probably a mistake in the design of the database.

[ PICTURE . As it turned out in the comments and editing the question, the OP had a valid reason for saving the three tables. Further tips on this are removed.]

To solve your problem, you can use UNION or UNION ALL:

 (SELECT 'photo' AS item_type, date_added, user_id FROM photos) UNION ALL (SELECT 'video' AS item_type, date_added, user_id FROM videos) UNION ALL (SELECT 'comment' AS item_type, date_added, user_id FROM comments) ORDER BY date_added DESC 

Answer 3

If it were me, I would simply execute a query on each table and select N the most recent elements (sort by date_address restriction in descending order of N), and then combine them in PHP. Thus, you are protected if you need to place tables on separate physical machines.

You may also ask yourself why you need 3 tables. Perhaps a single table with an activity_type window is enough. This will probably make it easier to add new types of activity later.

Answer 4

Personally, I would make another table to hold any activity. This would simplify a lot, and you could also track deletions and other actions.

Answer 5

Make UNION in the following tables:

 (SELECT "photo" AS `table`, `date_added` FROM `photos` ORDER BY `date_added` LIMIT 10) UNION (SELECT "video" AS `table`, `date_added` FROM `videos` ORDER BY `date_added` LIMIT 10) UNION (SELECT "comment" AS `table`, `date_added` FROM `comments` ORDER BY `date_added` LIMIT 10) ORDER BY `date_added` DESC LIMIT 10;