User recent activities - PHP MySql

Question

Hi everybody!

Here's my problem: I have 3 MySql tables representing: photos a user post, videos a user post, comments a user post and I need to view the 10 (20, 30, 40...) most recent activities by the user. For example in photos table may be composed by:

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 

the videos 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

the comments 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 the 3 tables have different number of attributes, so how can I do the union? and while fetching the query result how can i understand to wich table it belongs to?

So in the user profile page I'd like to show his recent activities of course ordered by DATE DESC this way:

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 anyone help me please?

Answer 1

A MySQL UNION query could 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'd wind up with a result set like

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

and so on. (actually you can leave user_id out of the SELECT if you want, of course)

Answer 2

Do a UNION on these 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;

Answer 3

If it was me, I would just do a query to each table and select the N most recent items (order by date_added desc limit N), and then merge them in PHP. That way, you are protected in case you ever need to host the tables on separate physical machines.

You may also ask yourself why you need 3 tables. Perhaps 1 table with an activity_type column would be enough. That would probably make it easier to add more activity types later.

Answer 4

Personally, I would make another table to store any activity. It would simplify things a lot and you could also keep track of deletions and other activities.