I have a social networking site where I just started logging certain user actions to a table.
Actions such as when they update their mood, edit their profile and update new photos.
I want to integrate a sort of news feed to a user's profile where it will show these updates for users they've added as their favorites.
The issue I'm running into is that if a user updates their profile four times in a half an hour, I'd like to only show that as one update.
Same goes for photos. If a user adds four photos within an hour, I want that to group that as one item.
The structure for the update log (update_log) is as follows:
update_type (1=profile update, 2=photo added, 3=mood updated)
pictureid INT (joins to userphotos table with file information)
mood_a INT (joins to lookuptable that holds first part of selectable mood)
mood_b INT (joins to lookuptable that holds second part of selectable mood)
mood_c INT (joins to lookuptable that holds third part of selectable mood)
Typical records for the three scenarios are as follows:
(auto), 1, 1, 0, 0, 0, 0, 1238598393
(auto), 1, 2, 10000, 0, 0, 0, 1238598393
(auto), 1, 3, 0, 5, 10, 15, 1238598393
I'm pretty sure this can't be done with a query as I need the individual photo file information to display thumbnails.
What's the best way to organize and sort through this information in PHP?
My brain is failing me on this.
Below is the MySQL query I wrote to grab all the pertinent data I need to display this feed for updates in the past 48 hours and attached to this post is some sample data returned from that query,
a.description AS mood_a_description,
b.description AS mood_b_description,
c.description AS mood_c_description,
FROM update_log l
JOIN update_types t
ON l.update_type = t.code
LEFT JOIN userphotos p
ON l.pictureid = p.pictureid
LEFT JOIN lookuptables a
ON l.mood_a = a.code AND
a.tableid = 'mood_a'
LEFT JOIN lookuptables b
ON l.mood_b = b.code AND
b.tableid = 'mood_b'
LEFT JOIN lookuptables c
ON l.mood_c = c.code AND
c.tableid = 'mood_c'
WHERE FROM_UNIXTIME(update_time) > now() - INTERVAL 48 HOUR
ORDER BY l.update_time DESC