Querying data for a Facebook-like news feed

Question

I have a social networking site where users update their moods, profiles and add photos.

I'm currently logging all updates in a table called "update_log" with the following structure:

update_id int (auto), 
userid int, 
update_type int (1=profile, 2=photo, 3=mood)
pictureid int
mood_a int
mood_b int
mood_c int
update_time int

Profile update record: (auto), 1, 1, 0, 0, 0, 0, 1239003781

Photo update record: (auto), 1, 2, 11544, 0, 0, 0, 1239003781

Mood update record: (auto), 1, 3, 0, 1, 490, 70, 1239003781

For the photo record, there's a corresponding table userphotos which holds the caption and filename/location data

For moods, there is a mood lookup table that holds the mood descriptions (i.e., I'm lazy =\ )

What I need to do is query this data to show on a user's profile page, it will show this feed for any of their favorite users for the last x hours of activity.

The problem I'm running into is that if a user uploads five photos over the course of a half hour or something, I just want that to be one line in the feed, not an entry for each photo upload.

Same goes for profile updates.

I need to query the data so the user will see something like this:

user x updated their mood! (I'm tired) on Apr 4, 2009 10:35 pm
user y uploaded x new photos on April 4, 2009 10:20 pm
user x updated their profile on April 4, 2009 10:15 pm

How do I group the photo updates into one record returned in a query based on all records being within let's say an hour of each other?

Is there a way to do this with one query?

Thanks!

Answer 1

Have you considered trying to do this with PHP rather than SQL queries? It might be less complex to query the results you need (All updates between these times) and then use PHP to compare the timestamps in order to determine how they should be grouped.