0

I will be running a Cron Job on my network to automatically add a "rough" logout time for users who have not used the logout button. I need a little help with the final coding.

On my ap_users database table, there is a row for each user called current_status which can be 0 for not active or 1 for active. This status is updated on each page of my network.

I also have a database table called ap_login_history which adds a row when a user logs in, and has a row for loggout_time showing as 0000-00-00 00:00:00 until the user hits the logout button.

To fix the empty time if a user doesn't click the logout button, I will be running a Cron Job every few hours to update the current_status to 0, wait 300 seconds to see if the user is still active and then check again to see if the email address which was changed to 0 has not been updated to 1.

If the user IS NOT active (i.e The update returns that the current_status is still 0 where it was updated by the cron job) add a timestamp to the logout_time in the ap_login_history table.

Here is my cron job script:

    //// RESET ANY ACTIVE USERS TO NON ACTIVE
$sql = "SELECT * FROM `ap_users` WHERE `current_status` = '1'";
 $result = $conn->query($sql);

  if ($result->num_rows > 0) {
 // output data of each row
while($row = $result->fetch_assoc()) {
        $user_id = $row['user_id'];
        $email_address[] = $row['email_address'];
        mysqli_query($conn, "UPDATE `ap_users` SET `current_status` = '0' WHERE `current_status` = '1' AND `user_id` = '$user_id'");                                        
}
}
//// GIVE USERS A CHANCE TO SHOW AS ACTIVE IF STILL LOGGED IN
sleep(300);
$timestamp = date("Y-m-d H:i:s");
//// UPDATE LOGOUT TIME FOR USERS WHO ARE NO LONGER ACTIVE
foreach ($email_address as $value) {
    $sql = "SELECT * FROM `ap_users` WHERE `current_status` = '0' AND email_address = '$value'";
 $result = $conn->query($sql);

  if ($result->num_rows > 0) {
 // output data of each row
while($row = $result->fetch_assoc()) {
        $email_address = $row['email_address'];
        mysqli_query($conn, "UPDATE `ap_login_history` SET `logout_date` = '$timestamp' WHERE `email_address` = '$email_address' AND `logout_date` = '0000-00-00 00:00:00'");                                       
}
}
}

This script DOES work, although it's temperamental with the sleep() setting. Anything under roughly 130 will succeed, any longer will cause a Internal Server Error. It really isn't a good idea to use the sleep() function here.

Is there any other way to control the wait time here or perform this function where it will be 100% successful? Maybe even clean it up a little?

Thanks in advance

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Snappysites
  • 804
  • 1
  • 10
  • 41

1 Answers1

1

For my previous project, I didn't use cron job to do this type of work. I just using a timer in Javascript and will check the status of the user and update the time every time user click the link or process to other pages.

The basic idea is:

  1. When user login, the database will record the login datetime and active datatime. The javascript will start to countdown from the predefined time (E.g. 300 seconds).
  2. After the time is reach, Javasript will alert a message and force user to log out the account using redirect pages. The redirect pages will update the logout_time=now() in ap_login_history. Besides, it will set the current_status=0
  3. Second condition->When user refresh or move to another pages or links, the timer will reset again. So no need do any logout action.

However, it may not help when user close their browser.

So, I suggest you set a cookie or session for every user. The basic steps is:

  1. Set a cookie with respective user identify such as userID with defined time limit(e.g. 300 seconds) setCookie References
  2. Every time user reload or go to other pages, you check whether the cookie still there or not. If yes, you update the new date time to the cookie, it should be same as you set the cookie at previous step. If no, that means your user already not active then you may using javascript to redirect the pages to log out pages and do the thing you want to do such as update the database.

Therefore, there are no need to set the cron job for the expire session.

Hope this can help you!

Matthew Chin
  • 171
  • 1
  • 12