在用户输入日期和时间时修改mysql DB

Question

im working on a quiz web application in php and mysql, i have a user table and quizes table, the admin has access to a table with the quizes name and a checkbox besides every quiz...

lets say theres 10 quizes numbered from 1 to 10, each one of this corresponds to a quiz in mysql,

when you click on the checkbox besides a quiz and hit SEND, that particular quiz is available to a certain user (that on a previous page i already chose) but on that same page i want to put input fields with a time and a date, and that quiz will only be available from the present time to the input date and time....

the user table has a column for every quiz named is_available with default to zero, i want that value change to 1 when that user and quiz are chosen and change back to zero when the date has passed..

i have been thinking how to do this but i have no clue, any ideas or guidelines of where to go or how to start doing this??? i need heeeelp! hehe

Answer 1

You can do this using MySQL, PHP, and CRON

First, create a relational table -

CREATE TABLE `test_expiry` (
    `userid` int(2) unsigned NOT NULL,  // user that is taking the test
    `testid` int(2) unsigned NOT NULL,  // test number
    `expires` datetime NOT NULL);  // date/time test will expire
       //change names/types as needed / to match your db structure

Second, when setting is_available = 1, add that test to the test_expiry table

INSERT INTO `test_expiry` (`userid`,`testid`,`expires`) VALUES (:userid,:testid,:expires)

Third, create a php page that will update is_available = 0, if date/time has past.

SELECT `userid`, `testid` FROM `test_expiry` WHERE `expires` < NOW()
foreach row above ...
UPDATE `tests` SET `is_available` = 0 WHERE `userid` = :userid AND `testid` = :testid

And last, set up a cron job-

* * * * *  [/path/to/php/]php /path/to/your/script/update.php