Most efficient way to make an activity log
- by Nathan
I am making a "recent activity" tab to profiles on my site and I also am going to have a log for moderators to see everything that happens on the site. This would require making an activity log of some sort.
I just don't know what would be better. I have 2 options:
Make a table called "activity" and then every time someone does something, add a record to it with the type of action, user id, timestamp, etc.
Problem: table could get very long.
Join all 3 tables (questions, answers, answer_comments) and then somehow show all these on the page in the order in which the action was taken.
Problem: this would be extremely hard because I have no clue how I could make it say "John commented on an answer on Question Title Here" by just joining 3 tables.
Does anyone know of a better way of making an activity log in this situation? I am using PHP and MySQL. If this is either too inefficient or hard I will probably just forget the Recent Activity tab on profiles but I still need an activity log for moderators.
Here's some SQL that I started making for option 2, but this would not work because there is no way of detecting whether the action is a comment, question, or answer when I echo the info in a while loop:
SELECT q.*, a.*, ac.*
FROM questions q JOIN answers a ON a.questionid = q.qid
JOIN answer_comments ac ON c.answerid = a.ans_id
WHERE q.user = $userid
AND a.userid = $userid
AND ac.userid = $userid
ORDER BY q.created DESC, a.created DESC, ac.created DESC
Thanks in advance for any help!
