set status on record automatically after certain datetime

Question

I have a form that users fill in to post their job advertisements and one of the fields they enter is the number of days to keep job 'open'.

I want to be able to automatically change the status of the job to 'closed' after the days that they have entered have elapsed.

The job table is already set up with status and expiry, I just need to know how to automatically change the status from 'open' to 'closed'.

I have little knowledge of MySQL and the queries that I would need to run.

Answer 1

Suggest that you don't keep status as a field in the database, but rather derive that status from the expiry date in your business or presentation tier. This could be done in your SQL code or in client code(PHP).

Some pseudocode:

INSERT INTO JobAd (Description, ExpiryDate)
VALUES (@Description, @Today + DaysToLive)

...

SELECT *
       , CASE WHEN ExpiryDate<@today
       THEN 'Expired'
       ELSE 'Good to go'
       END AS JobStatus
FROM JobAd  ORDER BY JobStatus, ExpiryDate

When you pull those jobs out, use PHP to write the status back to the user, based partially on ExpiryDate. Again, this could be done in SQL or PHP.

Having the status derived means you won't have to create a maintenance process/task to go update an attribute in the database.