CODEWITHSUNDEEP
mysql
Vera Perrone
Vera Perrone

Reputation: 369

How can I automatically change one value after x hours?

I am currently working on a ban/unban feature for a school project. I have one User table with one is_banned column. Whenever I do one Update query to change the status from unbanned to banned I want to pass a value containing for how long the user is banned. Let's say 24 hours. The thing is I want to automatically unban him after that time. Is this possible?

Upvotes: 0

Views: 95

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Well, banning is probably not a common occurrence, so you could use events. When someone is banned, create an event to unban the person.

I still don't like that idea. The database might happen to be down when the unban job is supposed to run. The user might be banned again, extending the ban period.

I think a better way is to have a column in the table (say users) with the most recent ban time. Then create a view to get the unbanned users:

create view v_users as
    select u.*
    from users u
    where u.bantime is null or u.bantime < now() - interval 24 hour;

The key is to use this view whenever your code wants unbanned users.

Or, if you just want a flag:

create view v_users as
    select u.*, (u.bantime > now() - interval 24 hour) as isBanned
    from users u;

Upvotes: 1

Related Questions