How to log user actions with php and mysql?

Question

I'm working on my CMS and I want it to log activities by users and other admins. For example: when new user registers or admin makes a new news post -> update last activity.

I want to know what is the best and easiest way.

Answer 1

• Create a table in your database to log your user activity.
• Define the various activity types that can happen in your App.
• Create a common function that logs any activity to that table.
• Call that function from anywhere you perform log-worthy activities in your app.

You can then write a reporting tool that gives your admins access to those logged activities, you can filter by user, time and activity types.

In my log-framework, I specially mark activities which could be seen as malicious actions and assign them different numeric threat-values. If the sum of a user's thread-value reaches a certain threshold I log-out the user.

Ideally if you write an Application, you write your infrastructure code like logging at the very beginning and then use it in all your business logic code later.

Edit for cleanup:

Over time you may collect lots of records in that table. Depending on your requirements you could do different things.

• Delete any entries older than x days (maybe a year)

• Delete any entries of certain types older than x days, but keep entries of other types for longer, or forever.

• Move entries older than a certain threshold into an archive log table. This keeps your main table small but allows you to access older log data if you really have to. I have a checkbox Use archive on my review logs page.

Answer 2

Basic Answer

Instead of doing this yourself, from scratch, check out how some existing systems do it, and if their license allows, use their design and code (make sure you document what code you've used and add a copyright notice to your CMS somewhere).

Possibly Helpful Example

I'm not sure about PHP CMS's which do this, but I know Django's admin app does. Django is implemented in Python, but it should be fairly straightforward to port this code over to PHP. Even if the code isn't a straight port, the design could be ported.

The file which contains the logging is in the admin module in models.py.

Some key aspects:

The data model for the logging table:

class LogEntry(models.Model):
  action_time = models.DateTimeField(_('action time'), auto_now=True)
  user = models.ForeignKey(User)
  content_type = models.ForeignKey(ContentType, blank=True, null=True)
  object_id = models.TextField(_('object id'), blank=True, null=True)
  object_repr = models.CharField(_('object repr'), max_length=200)
  action_flag = models.PositiveSmallIntegerField(_('action flag'))
  change_message = models.TextField(_('change message'), blank=True)
  objects = LogEntryManager()

And the LogEntryManager, which saves the actual log entries:

class LogEntryManager(models.Manager):
  def log_action(self, user_id, content_type_id, object_id, object_repr, action_flag, change_message=''):
    e = self.model(None, None, user_id, content_type_id, smart_unicode(object_id), object_repr[:200], action_flag, change_message)
    e.save()

Answer 3

I use two tables for activities, one that gives each activity an id, and another one that just logs the user id, activity id, and a timestamp. I do this because an int takes up less space than a string, so why log the same strings over and over? The second one isn't really necessary, you just just as easily keep the action codes in a text file for your own reference, but the db just seems like a easier place to remember.

In the past I've used a function to handle the actual logging actions, but the next time I do it I'm going to be using the Observer pattern. It appears to be a lot more flexible, and I've already had to edit out logging function calls from older code I have that wasn't going to log anything. I much prefer reusing code with no editing required.