cronjob/trigger which can update the database everyday

Question

Hi,

I am using MYSQL as my database and PHP as my programming language.I wanted to run a cron job which would run until the current system date matches the "deadline(date)" column in my database table called "PROJECT".Once the dates are same an update query has to run which would change the status(field of project table) from "open" to "close".

I am not really sure if cron jobs are the best way or I could use triggers or may be something else.Also I am using Apache as my web server and my OS is windows vista.

Answer 1

If your OS is Vista, the equivalent of a cronjob is a scheduled task. MySQL doesn't have a facility for running a trigger on a date change so a PHP script doing the same is a good choice.

All you have to do is open the scheduler (Control Panel > System And Maintenance > Administrative Tools > Schedule Task) and create a new task.

For example, if PHP is installed in C:\PHP\ and your script is in C:\htdocs\tasks\close_projects.php, you need to create a task with the following action:

Action: Start a program
Program/script: C:\PHP\php.exe
Add arguments: close_projects.php
Start in: C:\htdocs\tasks\

Adjust the rest of the settings based on your needs. (For your example, you want to use a daily task).

---

Another option is to do your task on the first request of the day. In which case you need to add some PHP code that runs on each page load. Simply store the date of the last request, and if the current request was made yesterday, run your SQL statement.

Answer 2

UPDATE project SET status='closed' WHERE deadline<=CURDATE();

Setting up a scheduled task to run this query should be trivial.

Answer 3

Because of lack of cron on some hostings, I once wrote set of classes for "delayed tasks". I realized that some tasks does not have to be run the precisely the time you specifify, but they can be postponed to the first time some client requests the server.

Hope it might help – ZIP.

Answer 4

What version of mySql are you running ?

mySql supports temporal triggers since 5.1.6

http://dev.mysql.com/tech-resources/articles/event-feature.html

.k