I'm working on a website, and I'm trying to archive a "logout" in my SQL DB.
My plan, and what I got already.
The user is logging in, the normal way: username and password. It works. There is a users_login_state table in my SQL DB, and when a user logs in, login page inserts a line into this table, with username, userid, date, time, token, etc. I got this, this works as well. On LOGOUT, the reverse happens: my page deletes the user from this list in the table.
Every time, the user makes an action, navigate, comment, etc, an update happens in DB in that table: the time gets refreshed in the particular line.
What I'm trying to achieve is: a server sided stored process: a job in the DB, what repeatedly checks that table, maybe in every 15 or 30 minutes: if there is a user in the table who's been inactive in the last 15 minutes, and all the users gets deleted from this list, who is not active more than 15 minutes ago.
More details: i use these date and time formats in php:
$date = date("Y-m-d"); // Year - Month - Day
$time = date("h:i:sa");
what results in:
2017-04-26
and
22:03:46pm
When I insert a login data on login, i use this code:
$registerThisUserToOnline = mysqli_query($connect, "INSERT INTO users_login_state VALUES ('', '$userMainID', '$user_login', '$loginTpye', '$date', 'NOW()', '$ipAddressData', '$tokencode')");
(I tried to the user the pre set $time variable but got the same format of time inserted into the table, I wrote before.
So I tried to make a job in DB and tried a few codes found on the net (I'm a beginner in stored procedures)
Why this way: because I want to make a mail section, and want to show the "last online" time in the list of users - or if online, "online", and I want to avoid "stuck" user logins, when a user logs in, and just closes browser without logout, remaining logged in in the DB.
I would appreciate any help on making that job in DB. Thanks in advance!