I have a logs table that contains activities of members using my website. I use the logs table to display recent items for particular items. i do this by storing the a seperate table id and that tables name in the logs table. i then use a while loop on the logs to grab data from the individual tables. Currently i have about seven different tables. To avoid duplication of data when a user updates anything i have grouped by the tables id on the logs sql request. However, i run into a problem with photos. I allow users to upload as many photos as they wish. each photo adds a logs entry with the table name of 'photos' and their associated photos_id which is the same as an 'id' with auto increment on the photos table. However if i group by tables id then each of these individual photos is displayed. i want to eliminate this and just display one item for photos uploaded on the same date. Therefore i was thinking of adding a second group by on a formatted (y-m-D) datetime field in the logs however i quickly realized that would not work as the photos are all going to have a unique id but have the same date. How can i accomplish something along these lines?
The SQL i an using is
SELECT * FROM logs WHERE user='".$_SESSION['username']."' AND ( db_name='blog' OR db_name='bucket_list' OR db_name='destinations' OR db_name='drinks' OR db_name='games' OR db_name='music' OR db_name='news' OR db_name='photos') GROUP BY db_id, DATE_FORMAT(date,'%Y-%m-%d') ORDER BY id DESC LIMIT 20
db_name = name of individual tables
db_id = auto increment value assigned to each post in their respected tables
$_SESSION['username'] = name of current user which is also in logs table
thanks in advance!!