用户最近的活动-PHP MySql [英] User recent activities - PHP MySql
问题描述
这是我的问题:我有3个MySql表,分别代表:用户帖子的照片,用户帖子的视频,用户帖子的评论,并且我需要查看10个(20、30、40 ...)的最新活动.用户. 例如在照片表中可能由以下内容组成:
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
视频表
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个表具有不同数量的属性,那么我该如何进行联合呢?在获取查询结果的同时,我该如何理解该表所属的表?
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?
因此,在用户个人资料页面上,我想当然地显示出他最近由DATE DESC排序的活动:
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
有人可以帮我吗?
推荐答案
MySQL UNION查询可以在这里工作:
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
,依此类推. (当然,您当然可以将user_id
留在SELECT之外)
and so on. (actually you can leave user_id
out of the SELECT if you want, of course)
这篇关于用户最近的活动-PHP MySql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!