Harry Pen Harry Pen - 1 year ago 106
SQL Question

How to automatically log out a user after period of time?

I am fairly new to website creations, However I have made a website and would like display who is Online, and who is Offline. with the best method, and Log them out, if they are inactive for so long. As of current, When someone logs in it updates the column

. Then when someone logs out, it will update

I also do have a timestamp in the database which tells me when they logged in, and when the logged out. (and as I said, Im a bit new to all this), as someone has said, why should i need both
. I just thought it may be easier. This is the code I have...


session_regenerate_id (true);
$_SESSION['user_id']= $id;
$_SESSION['user_name'] = $user_name;
$_SESSION['login_time'] = time();
mysql_query("update users set online='0' where login_time >= 60");
mysql_query("update users set online='1' where id='$id'") or die(mysql_error());
header("Location: home.php");

Would there be a better way to do this? and include an easier method to update someone as offline after 5 mins of inactivity?

Answer Source

In case something goes wrong, try to tie down the problem by debugging it. First, try to run your command in a mySQL console:

mysql> UPDATE users set online='0' WHERE login_time = > 60;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '> 60' at line 1

The correct syntax for greater-than is >= (the lower/greater always comes first).

login_time >= 60 would also require you to update the time every second manually which creates a high database load. Your UPDATE command would need to be run once a second to catch all users.

You should consider storing and matching the login (or last action) datetime instead:

On Login:

UPDATE users SET login_time=NOW() WHERE id=?

Check if a user is still online:

SELECT * FROM users WHERE login_time < DATE_SUB(NOW(),INTERVAL 5 minute)

See the mySQL documentation at https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add for more about DATE_SUB and INTERVAL.

PS: The PHP manual states This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. about mysql_query.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download