php - 用户近期事件 - PHP MySQL

Question

我有 3 个 MySQL 表，代表:用户发布的照片​​、用户发布的视频、用户发布的评论，我需要查看用户最近的 10 次(20、30、40...)事件。

例如在照片表中可能由:

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 

视频表

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

评论表

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

如您所见，这 3 个表具有不同数量的属性，那么我该如何合并呢？在获取查询结果时，如何了解它属于哪个表？

所以在用户个人资料页面中，我想按 DATE DESC 这样显示他最近的事件:

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

谁能帮帮我？

Answer 1

MySQL UNION 查询可以在这里工作:

(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;

然后你会得到一个像这样的结果集

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

等等。 (当然，如果你愿意，你可以将 user_id 留在 SELECT 之外)