Update record status based on Time

I am looking for some guidance. Basically I'm developing a system for a Jobs database. Users can apply for jobs advertised on the site.

When someone submits a new job they will need to specify the close date/time for applications. This will typically be the date and the hour, eg: 16:00 on 20/01/2011

My question is, how to determine whether a job is still open or closed. There are two ways I can think of doing this:

1. Have a status field in the table, status 1=open, status 0=closed. Now the issue with this is that the status would need to be automatically set to 0 once the close time has been reached.

2. Use PHP to check whether the close time is greater than the current time - if it is then it means it is closed and users cannot apply for the job.

In addition, the owner of the job will be able to re-op开发者_开发知识库en closed jobs, or manually close a job before the close date.

What would be the best way of doing this?

Use option 3: only query for unexpired jobs; you can test if a job is expired by comparing NOW() against the stored date time, if NOW() < expire_time then the job is open.

I'd use both approaches. The status field can be used to activate a job, or deactivate in the future. Perhaps it can take on more than one status, such as "draft", "active", "closed".

The time feature is nice because you can use that to automatically close the job with your PHP process.

Just make sure that times are all compared in the same timezone, to keep your life simple when determining what jobs should be closed.

Your assumption that "status would need to be automatically set to 0 once the close time has been reached" isn't true.

What you need to do is simply query the database for live jobs that are within the start and close date/time. For example:

SELECT <fields> FROM <job table> WHERE
            job_startdate <= NOW() 
            AND job_enddate >= NOW()
            AND job_status=1 
            ...

This will ensure that only open jobs that are within the start and end dates will be displayed.