#72 Update user timestamp at login

[Anonymous]

Hi,
I don't know if you're interested but I have changed the code slightly to update the timestamp for the user when they login. We have many users on our system and I couldn't find an easy way to determine when they last logged in. I would like to remove the accounts which are no longer used.

This only works with the default MRBS user table or when using a MRBS user table as an external database (e.g. multiple sites with 1 login). I changed the following files in ver 1.7.1 to make it work.

For the normal MRBS user table change auth_db.inc in function authValidateUser add

global $tbl_users;

to the top of the function (line 126). It should appear on the line before $valid_usernames = array();

After $valid_usernames = array_unique($valid_usernames); at roughly line 136 add

if (count($valid_usernames) == 1) {
    $sql = "Update $tbl_users SET timestamp=NOW() Where name =?";
    db()->query($sql, array(utf8_strtolower($valid_usernames[0])));
  }

If using an external MRBS table structure, provided there is a column called timestamp make the following change to auth_db_ext.inc. In function authValidateUser at around line 130, prior to return $retval; insert:

//if password matched then update timestamp
    if ($retval != false) {
        $sql = "Update " . $db_ext_conn->quote($auth['db_ext']['db_table']) . " SET timestamp=NOW() Where name =?";
        $stmt = $db_ext_conn->query($sql, array(utf8_strtolower($user)));
    }

Regards

[GedM]

I don't know of you can change the creator of a post, I forgot to login when I entered the post above.

[John Beranek]

You can't change the creator of a ticket, it seems - SF probably don't like the idea of changing history that much...

As for the change, I'm not sure we'd do it that way, as the 'timestamp' field was added to track changes to a user, not logins.

I'd rather add an explicit 'last_login' column to the users table, both "db" and "db_ext" auth schemes would then need additional code to support that column (optionally in the case of the latter).

[GedM]

Hi John,
Your solution is a better way to do it, but I wasn't too fussed about the last modified time of the user and thought this was an easy hack to get a result.
I'll look at implementing it your way when I have a moment and then post the patch back here.
Thanks

[John Beranek]

I've got a somewhat working patch, minus the database upgrade code.

However, thinking out loud I'm not sure if the DB value should be in UTC, and also whether the login time should be displayed to the user as UTC, or the local time according to the global timezone configured in MRBS...

[John Beranek]

I'd add that storing DATETIME values in MySQL and PostgreSQL with timezone considerations is a minefield, and I remind myself why MRBS generally uses POSIX timestamps, and leaves all timezone manipulation to PHP!

[Anonymous]

Hi John,
Thanks for the change. I wasn't expecting you to make the changes for me, but I appreciate it.
if you're asking for my opinion. It would be a better solution to show the last login datetime as timezone corrected. However storing the value in MYSQL should be done in the most robust way. I don't see any value in manipulating the timestamp to match the particular timezone and then storing the adjusted timestamp in MYSQL.
Just my 2c worth.