Delete records after 15 minutes

问题:

I've got an inactive system. It is supposed to delete users from inactive table after 15 minutes. I have also a code that logs the user out if it cant be found in that table. The inactive script updates user on every page refresh.

This is the inactive code I tried and it doesn't work so far:

$result = mysqli_query($con,"SELECT * FROM inactive");

while($row = mysqli_fetch_array($result))

if ($row['inactive'] > timestampadd(MINUTE, -15, now()))
  {



    }else {

$db_query = "DELETE FROM inactive WHERE username='$username'";
$result = mysql_query($db_query);


    }

回答1:

CREATE EVENT IF NOT EXISTS `remove_inactives`
ON SCHEDULE EVERY 15 MINUTE
ON COMPLETION PRESERVE
ENABLE
DO
    DELETE FROM `inactive`
        WHERE `timestamp` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
;

^ Use a set and forget recurring MySQL Event. Let the server do the work for you :)

For PHP:

mysqli_query($connection, // or mysql_query(
<<<SQL
    CREATE EVENT IF NOT EXISTS `remove_inactives`
    ON SCHEDULE EVERY 15 MINUTE
    ON COMPLETION PRESERVE
    ENABLE
    DO
        DELETE FROM `inactive`
            WHERE `timestamp` < DATE_SUB(NOW(), INTERVAL 15 MINUTE)
    ;
SQL;
); // ends *_query() call

You just need to RUN THIS ONCE to install on the server!

回答2:

If you have a timestamp field you can just do a single query and not have to iterate over the result set from your select:

DELETE FROM inactive
WHERE timestamp < DATE_SUB(NOW(), INTERVAL 15 MINUTE)

You can execute that query in a cron or on your page.