How to auto update a record in database?

Question

I have a jobs table in which I inserts new jobs. This table also contains the job post date.

By default the job status is open when a new insertion take place.

Now I want to change the status of the job from open to close when the jobs becomes older than 30 days.

How will I do this?

Answer 1

Try creating a event which runs every day like below

CREATE EVENT myevent
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
  DO
    UPDATE my_table SET status ='closed'
    WHERE post_date > DATE_ADD(now(), INTERVAL -30 DAY)
    AND status='open'

-- Update Changed syntax

  CREATE EVENT myevent
  ON SCHEDULE EVERY 24 HOUR
  DO
    UPDATE my_table SET status ='closed'
    WHERE post_date > DATE_ADD(now(), INTERVAL -30 DAY)
    AND status='open'

Answer 2

Use a cron job to handle this. http://en.wikipedia.org/wiki/Cron

If you can't handle crons, you could do a "poor man's cron". Means the actual process that updates all of your jobs records takes place when someone visits your page (with additional checks when the last run was). If you're doing it the "poor" way I suggest to fire off another thread to keep your site responsible.