I was looking for a way to create a historical log of every member that logged into an ExpressionEngine site. I came across the Statistics module which I was hoping would do the job, but this module only worked to display statistics of users currently logged in to the site, very much akin to what you would see in the footer of a forum.

What I needed to do in this case was to log a timestamp of when a member logged in to the site, and the members ID so that I can pull other data available on that member to generate reports at a later date.

So, first of all I need a new database table to hold this data. It’s very simple, and here’ the SQL I used to create it:

CREATE TABLE `UserLog` (

  `ID` mediumint(9) NOT NULL auto_increment,

  `MemberID` mediumint(9) NOT NULL,

  `LoginTimestamp` int(10) NOT NULL,

  PRIMARY KEY  (`ID`)

)

Now that we have our database table, we need the code to fill it.

The code below was added into the page which the user is redirected to once they have logged in. We need to make sure that this template has ‘Allow PHP?’ is enabled and the ‘PHP Parsing Stage’ is set to ‘Output’ for this to work.

{exp:member:custom_profile_data}

  <?php

    if ({current_time} == {last_activity}){

      global $DB;

      $DB->query('INSERT INTO UserLog (MemberID, LoginTimestamp)

        VALUES ({member_id}, {last_activity})');

    }

  ?>

{/exp:member:custom_profile_data}

There we go, you can copy and paste to your hearts content, but if you’d like to know what’s going on, and perhaps learn a little something, please read on.

Firstly we need to get the {last_activity} timestamp from the member that is logging in. This is basically the most recent time the user has logged in to the site, so we employ the {exp:member:custom_profile_data} tag to let us grab it.

Inside this tag we include our PHP. We compare the {last_activity} timestamp to the {current_time} timestamp. If they match, we know the user has just logged in, and that’s when we want to add a record to our UserLog database table.

We then use the ExpressionEngine database class to insert the {member_id} and {last_activity} timestamp into the table.

So there we have it. You’re now logging every member as they log in to your site. Once you’ve got some data in there you can explore the possibilities of generating reports and statistics on this data. I’ll be doing this myself soon, so I’ll be sure to share my ideas on this.

  1. meteoracle posted this