CODEWITHSUNDEEP
phpmysql
AldoB
AldoB

Reputation: 401

User recent activities - PHP MySQL

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 which 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?

Upvotes: 9

Views: 5044

Answers (5)

Tomalak
Tomalak

Reputation: 338208

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

Upvotes: 3

Joe Phillips
Joe Phillips

Reputation: 51110

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.

Upvotes: 3

twk
twk

Reputation: 17320

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.

Upvotes: 1

Gumbo
Gumbo

Reputation: 655239

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;

Upvotes: 0

David Z
David Z

Reputation: 131570

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)

Upvotes: 11

Related Questions