apatik apatik - 6 months ago 16
SQL Question

Last time a user was logged in / browsing the website

I'm trying to set up a notifications system that doesn't requiere cookies.
I'm using SQL to store my users's info, and they are then passed into

$_SESSION['user_auth']
once they are logged in.

When a user logs in, I want to fetch the last time the user was online (for instance 05/05/2016 21:35:50) and then compare with the database if there's any more recent announcements, posterior to its "last time logged in".

Is this viable ?

How can I know the last time my user was browsing the website ? Do I need a new row in my
'users'
table, if so, how to set this function up ?

Thanks for your suggestions

Answer

I've got a similar method I created and you can see the answer here The basic idea is updating a lastActive column in the database and update this upon user login and set a session variable to the current time. Then at the top of each page is a function that checks a users activity. If the time between last login and current time is above 45minutes then the lastActive data in MySQL is updated.

You can use the following function to set and update the lastActive column and use your own methods for 'when' this function can be used.

function set_last_active($mysqli, $username) {
    if ($stmt = $mysqli->prepare("UPDATE Users SET lastActive = Now() WHERE username = ?")) {
        $stmt->bind_param('s', $username);
        $stmt->execute();
        $stmt->close();
    }
}

This is what I use as a script at the top of each page:

<?php
include_once 'functions.php';

if(isset($_SESSION['username'], $_SESSION['user_id'], $_SESSION['lastActive'])) {
    date_default_timezone_set("Europe/London");
    $now = new DateTime();
    $lastActive = $_SESSION['lastActive'];
    $diff=$now->diff($lastActive);
    $hours = $diff->format('%h');
    $mins = $diff->format('%i');
    $day = $diff->format('%d');
    $month = $diff->format('%m');
    $year = $diff->format('%y');



    if($mins > 45 || $hours >= 1 || $day >= 1 || $month >= 1 || $year >= 1) {
        $_SESSION['lastActive'] = $now;
        set_last_active($mysqli, $_SESSION['username']);
    }
}
Comments