自动更改记录

On the website i'm developing i'm making a system for ban and unban users.

In my database i have a table 'banned' with fields about the bans (userID, active, date was made, reason ecc).

What i want to do is:

Add another field for expire date, and when this date occur, change automatically the field 'active' to 0.

How i can do that?

I would not use two fields like you did -- because I would not want to depend on a task to change back the active field when the un-ban date is reached.

Instead, I would only use one datetime field, called like banned_until; and this field would either:

• Be NULL when the user is not banned,
• Or contain the date until which the user is banned.

Then, when the user tries to do something (log-in, post, ...), I would check that :

• This field is NULL, in which case the user is not banned
• Or that the date contained in this field is in the past, in which can the user has been banned, but is no longer.

In the second case, you could even reset the field to NULL, as the un-ban date has been reached.

Create a php script that will check if time is passed the expiration date. SQL will be something like this:

UPDATE banned SET active=0 WHERE expire_date<=NOW()

Save it as a for example task.php

Then create a cron task with crontab -e

*/10 * * * * php /path/to/your/taks/task.php

And this will cause this script to be executed every 10min and unban all banned ppl.

--

There are other ways, perhaps better ones, like e.g. Pascal described, but this answer is for your idea.

Its either you use a cron script or when getting banned users, you apply a where clause to check if the the ban has expired

You can compare expiry_date value with current_date to check if a user is active or not on his login.

SELECT 
 ( DATE_FORMAT( expiry_date_field, '%Y-%m-%d' )
   < 
   DATE_FORMAT( CURRENT_DATE, '%Y-%m-%d' )
 ) AS isActive
FROM
 banned 
WHERE
 user_id=?;  

A 0 returned represents in-active status and 1 as active.

But irrespective of a user's login, if you want to maintain active status of users, you can achieve this using the Event Scheduler.

Following example gives you an idea in implementing one.

drop event if exists event_user_bans_scheduling;
delimiter //

create event if not exists event_user_bans_scheduling
--  on schedule every 86400 second starts 00:00:00
--  at timestamp( adddate( current_date, 1 ),'00:00:00' )
  on schedule every 1 day starts timestamp( current_date + 1, '00:00:01' )

  comment 'Scheduler to update active status of users'
  do
    UPDATE my_app_db.banned
      SET ACTIVE=0
      WHERE
        date_format( expiry_date_field,'%Y-%m-%d' ) = date_format( CURRENT_DATE, '%Y-%m-%d' );
;
//
delimiter ;

Note:
The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. Read complete notes on Event Scheduler Configuration before working on MySQL events.