当今日期与截止日期匹配时，MySQL会触发

Question

I'm developing an application in CakePHP 2.4.7

I have my MySQL database and I've come to the necessity of triggering an update when the system's date and hour matches a due date I have in a table.

The table I'm using is the following

CREATE TABLE applied_surveys (id CHAR(36) NOT NULL PRIMARY KEY,
                        display_name VARCHAR(200),
                        area_id CHAR(36) NOT NULL,
                        survey_id CHAR(36) NOT NULL,
                        system_user_id CHAR(36) NOT NULL,
                        code VARCHAR(50),
                        init_date DATE,
                        due_date DATE,
                        init_hour TIME,
                        due_hour TIME,
                        completed INT,
                        state TINYINT DEFAULT 1,
                        max_responders INT,
                        created DATE, modified DATE,
                        FOREIGN KEY (area_id) REFERENCES areas(id),
                        FOREIGN KEY (survey_id) REFERENCES surveys(id),
                        FOREIGN KEY (system_user_id) REFERENCES system_users(id));

As you can see, I'm using an init date/hour and a due date/hour. My intention here is that I add a survey and I set a due date. When the due date and hour are reached the system must change my status(state) value to 0, meaning that the survey has been closed.

I'm integrating this database to a CakePHP application, but I'm not really sure where I should program the logic for this situation.

Answer 1

You can't write "sistem", it's system.

You will need to run a cron job every second/minute/hour, or whatever you prefer, that would check each record and see which ones are later than the system date. You can't expect it to run exactly at the time the dates become exactly the same, especially if you account for the seconds.

You can read about CRON jobs here : http://code.tutsplus.com/tutorials/managing-cron-jobs-with-php--net-19428