How to update field data in MYSQL database automatically

I have a mysql database with a table that has several fields including:

enter_date (which is a timestamp field - ie 2011-09-13 21:04:42) status (which defaults to active) period_allowed (which is a number of days ie 5 or 20 or 30 etc)

Now what I need to do is after the period_allowed (starting from the enter_date field) the status is to change from 'active'开发者_如何学JAVA to 'expired'.

ie if enter date: 2011-09-10- 12:00:00 period_allowed: 5

Then on 15 September the status is to be AUTOMATICALLY changed from 'active' to 'expired'

Ideas for best and easiest way to do this?

Thank you

P.S. Looking at setting up a cron job.. but coding to achieve this?

You can create a stored procedure to execute this update SQL, and create a MySQL Event (if using a recent version of MySQL) which runs that stored procedure periodically. Otherwise create a cron job that calls the stored procedure (or just runs the update sql) using the mysql command-line client.

UPDATE my_table SET status='expired' WHERE NOW() >= ADDDATE(enter_date, period_allowed)

See MySQL 5.1 Create Event documentation, it has a simple example for creating an event that runs an update statement. Or, to create a cron job, you can create an entry in your crontab that calls mysql using the -e flag to execute a statement.

hi you can use mysql event for update or delete data or update data after 1 days or 2 days i prepare a php script and that is :

$insertquery="CREATE EVENT JOY // joy is event name
    ON SCHEDULE  AT CURRENT_TIMESTAMP + INTERVAL 3 MINUTE //after 3 minute the event will run automatically
    DO
      BEGIN
       insert into tbl2 (col1, col2, col3) select col1,col2,col3 from course_entry where username = ''; //this is mysql job event should do copy data from tbl1 to tbl2 

delete from course_entry where username = '';// 2nd mysql job it will delete tbl1 data after insert process is done 
      END";

 mysql_query($insertquery) or die (mysql_error());

Hope it will work for you