How to create a temporary ban on an account? (Php / mysql)

Question

I want to create a function that allows me to deny an account for 10 days. In dbc, I have a field called "ban" and a Boolean of 1 = notban, 0 = ban. I also have a field called "date_banned", which is just the timestamp when the user was blocked.

My question is how to create a time interval of 10 days from the date the user was blocked?

ex: James was banned on the "2010-05-03 20:43:48". So how can I add 10days to a timestamp? And after 10 days, he would set the “ban” to 1 (which is not prohibited).

EDIT: how can I show how many days the user has left the ban? ex: another 8 days to unban

Can I ... do NOW () - $ date_banned? or how to subtract the ban date from the current date?

Answer 1

To add 10 days to the date_banned field in MySQL, you can simply use the DATE_ADD() function. You can perform the following check when a user tries to log in to see if the ban has expired:

 ... WHERE NOW() > DATE_ADD(date_banned, INTERVAL 10 DAY); 

Then you can switch the ban field when the user tries to log in. Otherwise, you can run the scheduled task every day or in any other way that checks expired bans and updates this field accordingly.

However, you really don't need the ban field to check if the user is denied or not. In fact, you may want to eliminate it. In fact, I went further and suggest using banned_until instead of date_banned (or use both of them). The banned_until field banned_until simplify your requests and allow you to predefine arbitrary prohibition periods at the time the ban is issued. In this case, when the user logs in, you can simply perform the following check:

 ... WHERE NOW() > banned_until; 

---

UPDATE:

To get the number of days remaining until the end of the ban, you can use the TIMESPANDIFF() function in MySQL:

 SELECT TIMESTAMPDIFF(DAY, NOW(), DATE_ADD(date_banned, INTERVAL 10 DAY)) ... 

Or, if you must use the banned_until field, it will be even shorter:

 SELECT TIMESTAMPDIFF(DAY, NOW(), banned_until) ... 

Answer 2

unban_date=DATE_ADD(NOW(), INTERVAL 10 DAY) should do the trick

Then there is just a cron that checks if someone was unban_date in the past and you can update your forbidden flag.