Question: I want cumulate or combine many comments into one if they are posted in similar time by the same user. What is the best way of approaching this?
Currently: I have DB table that has comments written by each user, structure as follow: ID, comment, timestamp, userid
Example: All users have a current status page, here you can view what they have been upto. For a example if the user uploads a new photo, comment will appear to show they have uploaded a photo. But if the user uploads many photos at once, let say 10 in the last 30mins, then there will be 10 different comments, but i want to combine or cumulate this into one comment instead of spamming the status page.
Technology used: MYSQL, PHP
thanks, if more info needed please ask.
you should not create multiple comments if it is all the same
make 3 tables:
photos [id, src, title, date, etc...]
comments [id, comment, timestamp, userid, etc...]
photos_comments [id, photo_id, comment_id]
when you upload multiple comments at once you will create only 1 new record in the comments table, but connect it to multiple photos in the photos_comments table (this is called many to many relationship)
when you want to get all the comments that related to a photo (lets say photo with id=10), you can pull it from the DB with this query:
SELECT * FROM comments WHERE id IN (SELECT comment_id FROM comments_photos WHERE photo_id=10)
or even better, with joins:
SELECT comments.* FROM comments INNER JOIN photos_comments ON photos_comments.comment_id=comments.id WHERE photos_comments.photo_id=10