Insert into MySQL table if no row added within 5 minutes

I have a table which contains a column consisting of timestamps. I want to check if there is a new row within 5 minutes period of time. If not, then insert a new row or update the last row status column.

I tried something like this:

UPDATE table
SET status = 'offline'
WHERE CURRENT_TIMESTAMP() > ((SELECT MAX(dateandtime)) + INTERVAL 5 MINUTE)

or insert into … but no success so far. I’m using PHP in WordPress if that matters.

Answer

I hope this helps you. If you want to add new row, you need to use INSERT command with selection like this…

INSERT INTO sessions(user_id, last_visit)
SELECT 1, CURRENT_TIMESTAMP
WHERE EXISTS (
      SELECT 1
      FROM sessions
      WHERE user_id = 1
      HAVING (MAX(last_visit) + INTERVAL 5 MINUTE) < CURRENT_TIMESTAMP
);

If you want to update status column, you need to use simple UPDATE

UPDATE sessions
SET status = 'offline'
WHERE (last_visit + INTERVAL 5 MINUTE) < CURRENT_TIMESTAMP;

Btw, you just need to have a last_visit column, and if it’s less than now + 5 min, you already know that the user is offline. And you have to update this value on every authorized request with simple update like.

update users set last_visit = CURRENT_TIMESTAMP where id = ?;

So your User class will be have a function getStatus() like this:

public function getStatus() {
    if ($this->lastVisit < (Carbon::now()->subMinutes(5))) {
        return 'offline';
    }    
    return 'online';
}