User recent activities - PHP MySQL

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.


Why do you have separate tables in the first place? That's probably a mistake in database design.

[EDIT: As it turned out through comments and a question edit, the OP had a valid reason to maintain three tables. Further advice about this 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

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)

Tags:

Mysql

Php

Recent Posts