update database table on session timeout in php

Question

I have a php code as shown below in which session timeout happen after 60 mins when there is no activity. The following code is inside the file /mno.php. My login and logout code is also in the same file /mno.php.

/mno.php

if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > 3600)) {
    session_destroy();   // destroy session data in storage
    !isset($_SESSION['pageadmin']);

    /* Update Table (START) */
    $open="false";
    $stmt= $connect->prepare("UPDATE trace_users SET open=? WHERE user_name=?");
    $stmt->bind_param('ss', $open, $_SESSION['user_name']);
    $stmt->execute();
    /* Update Table (END) */

    header('location: /mmo.php');
    exit();
}
$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp 

The table trace_users in the code keeps the track of all logged in users. In that table, there are two columns user_name and open. The value is set to true/false when any user log in/log out.

I have included sql query in which I am trying to update a table when there is no activity but unfortunately the column value is not getting set to false for that particular user when no activity happens for 60 mins.

This is what I have tried:

After doing some research, I think I have to run a timer (js/ajax). In the javascript shown below I have calculated the difference between the Last Activity and the Current time. If its more than 60 mins, then it will update a db table. This is what I have tried but I believe more need to be done in order to update a table in db.

<script>
let x = setInterval(function() {

    let lastActivity = <?php echo ($_SESSION['LAST_ACTIVITY']); ?>
    let now = <?php echo time() ?>;
    let difference = now - lastActivity;

    if (difference > 3600) {
        clearInterval(x);
    }
}, 1000
);
</script>   

Problem Statement:

I am wondering what changes I should make in the js (or php) code above so that when there is no activity for 60 mins, it should update the column open to false (in the table trace_users) for that particular user.

Edit 1:

My login code and session history code is in the same file /mno.php. I have placed everything in the same file /mno.php.

Answer 1

I think Vineys and jo0gbe4bstjbs answer is wrong because of when user close browser until 5 seconds, it can't update table after 60 mins and session too. Session deletes just after time in where set in php.ini configuration file. And Do you mind requesting every 5 seconds is it good way to solve this? It is worst for performance. If you want solve this problem with professionalism, you should add "last_request" column and delete "open" column from the table and after every request you should update last_requests value to current unix timestamp. And where getting users you should write:

$time = time() - 3600;

"SELECT * FROM `users` WHERE last_request > $time" //active users
"SELECT * FROM `users` WHERE last_request <= $time" //inactive users

And instead of ajax request every 5 seconds you should write setTimeout with 3600 second delay time which run window.location.href= '/mmo.php'; code. Its way good if you want best performance and exactly result with 60 minute logout

Answer 2

I suppose you realize that this code

if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > 3600)) {
    //...
}
$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp 

runs on every request and only when a request arrives

Imagine I visit your website and then go out shopping keeping the browser open. What do you think will happen? NOTHING - because there will be no new request sent to you (assuming you haven't implemented any periodic ajax polling / Websocket mechanism)

So the server won't bother about me until I come back from shopping and refresh the page, only then would the server realize "Hmmm..This guy's LAST_ACTIVITY is older than an hour let me update my trace_users table and set open as false for him"

Coming to your proposed solution, it looks good and avoids the complications of websockets/periodic ajax requests

Just need some minor corrections, follow here for a basic demo

<script>

    var lastActivity = <?php echo ($_SESSION['LAST_ACTIVITY']); ?>; //the timestamp of latest page refresh or navigation 
                                                                    //This will remain constant as long as page stays put
    var now = <?php echo time() ?>; //This takes inital value (technically same as LAST_ACTIVITY) from server 
                                    // but later on it will be incremented by javascript to act as counter
    var logoutAfter = 5; //I set 5 sec for demo purposes

    var timer = setInterval(function() {
                    now++;
                    let delta = now - lastActivity;
                    if ( delta > logoutAfter) {
                        alert('you are logged out');
                        clearInterval(timer);
                        //DO AJAX REQUEST TO close.php
                    }
                }, 1000);

</script> 

Here the lastActivity will hold the timestamp when the page was sent by server to browser it will be never changed by scripts on the browser, now is your counter that you will use to track how much time passed since page was loaded on the browser, you'll increment it every second and check if a given amount of time has been crossed

If true do a ajax request (or simply redirect to logout.php) where you would destroy session and update the trace_users table to mark the user as closed

UPDATE

So ajax will be like

$.ajax({      
    url: "/close.php", 
    type: 'POST', // GET also fine
    data: { },
    success: function(data) {
        window.location.href= '/mmo.php';
    },
    error: function(jqXHR, textStatus, errorThrown) {
        alert(textStatus);
    }
}); 

and

close.php

<?php
session_start();
$logoutAfter = 5; //5 sec timeout for testing purposes

// I'm not sure whether the below if condition check is required here or not 
// because we have already checked (whether to timeout or not ) in our javascript 
// and we call close.php only when it's affirmative
// I encourage you to test and find out :)

if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > $logoutAfter)) {
    session_destroy();   // destroy session data in storage
    !isset($_SESSION['pageadmin']);

    /* Update Table (START) */
    $open="false";
    $stmt= $connect->prepare("UPDATE trace_users SET open=? WHERE user_name=?");
    $stmt->bind_param('ss', $open, $_SESSION['user_name']);
    $stmt->execute();
    /* Update Table (END) */

    //header('location: /mmo.php'); //<-- no need of it when url hit by ajax
    exit();
}
else  //<-- note the else
$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp 

Answer 3

Page.php

<!-- CODE TO INCLUDE IN HEADER.PHP -->

<?php  
session_start();
$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp  
?>



<!-- CLOSE -->

<html>

<head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
</head>

<body>

</body>

<script>
let lastActivity = <?php echo ($_SESSION['LAST_ACTIVITY']); ?>; //the timestamp of latest page refresh or navigation
//This will remain constant as long as page stays put
let now = <?php echo time() ?>; //This takes inital value (technically same as LAST_ACTIVITY) from server+
                                // but later on it will be incremented by javascript to act as counter
let logoutAfter = 5; //I set 5 secs for demo purposes


let timer = setInterval(function() {
    now++;
    let delta = now - lastActivity;

    if ( delta > logoutAfter) {
        alert('you are logged out');
        clearInterval(timer);
        //DO AJAX REQUEST TO close.php
        $.ajax({
            url: "/mmo.php",
            type: 'POST', // GET also fine
            data: { },
            success: function(data) {

            },
            error: function(jqXHR, textStatus, errorThrown) {
                console.log("I am inside error");
                alert(textStatus);
            }
        });
    }
}, 1000); //<-- you can increse it( till <= logoutAfter ) for better performance as suggested by @"Space Coding"

</script>

</html>

mmo.php

<?php

$servername = "localhost";
$username   = "username";
$password   = "password";
$dbname     = "myDB";

$connect = new mysqli($servername, $username, $password, $dbname);

if ($connect->connect_error) {
    die("Connection failed: " . $connect->connect_error);
}

session_start();
$logoutAfter = 5; //5 sec timeout for testing purposes

if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > $logoutAfter)) {
    session_destroy();   // destroy session data in storage
    !isset($_SESSION['pageadmin']);

    /* Update Table (START) */
    $open="false";

    $stmt= $connect->prepare("UPDATE trace_users SET open=? WHERE user_name=?");
    $usname = !empty($_SESSION['user_name'])?$_SESSION['user_name']:'';

    $stmt->bind_param('ss', $open, $usname );
    $stmt->execute();
    /* Update Table (END) */

    //header('location: /mmo.php'); //<-- no need of it when url hit by ajax
    exit();
}
else  //<-- note the else
$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp  
?>

Answer 4

This is a simple time validation for web page:

$modified_on = isset($SERVER['HTTP_IF_MODIFIED_SINCE']) ? $SERVER['HTTP_IF_MODIFIED_SINCE'] : null;

$current_time = time();

if (!is_null($modified_on) && ($current_time - strtotime($modified_on)) > 3600) {
    session_destroy();
    ...
}

header('Last-Modified: '.gmdate('D, d M Y H:i:s', $current_time).' GMT');

...