Background info
I'm creating a donation/purchase system for a game garrys mod. when a user donates through paypal i have php code that handles the ipn and enters data into a database it also generates a serial key and emails this to the user who can then enter this in game, when the user enters serial in-game it checks the database sees if the key is valid and then executes the relevant lua code. The system so far works perfectly however the donation packages last for a certain amount of time 30 days, 90 days, 180 days and 365 days.
What i want to achieve is when the user enters the key in-game i will have lua code that stores the players steam id as well as the day they redeemed it and for how long there package will last for. i will setup a cron job to run a script that checks the database if the key is used, if the key has been used it will check to see if, for example, 30 days have past since they redeemed the key, if its below the 30 days do nothing if its over the 30 days then it will change a value in the table from 0 to 1.
Question
The part i'm really struggling to get my head around is how to store the date and check the date, initially i thought i would use day of the year but then comes the issue of a year change from 365 to 0 what if someone purchases it in December for 90 days then errors would occur. So how can i do this reliably accounting for the year changing?
Thanks to everyone who posted answers and extremely quickly as well, i think i have enough to work with for now, thanks again for your time
# Answer 1
Well, I don't know your database structure, but let's say it's something like this:
user_table
-----------------------------------------
user_id | date_of_redeem | subscription
-----------------------------------------
user1 | 2014-02-01 | 90
user2 | 2014-05-18 | 365
user3 | 2014-07-22 | 180
So, with using only SQL statements, you could use something, like DATE_SUB. Compare the date_of_redeem to today's date minus the subscription amount. If the value of the date_of_redeem is higher, then you would get a result, otherwise it would return an empty result set.
SELECT *
FROM `user_table`
WHERE `date_of_redeem` > DATE_SUB(NOW(), INTERVAL `subscription` DAY) AND `user_id` = 'desired_user_id'
Edit: SQL Fiddle for the above query: http://sqlfiddle.com/#!2/f95ea/11 (just change user3 to any of the user IDs to check if they return any results.
if you would like to get a table with all the subscription expiry dates, then you could do something like this:
SELECT `user_id` AS `User`, (DATE_ADD(`date_of_redeem`, INTERVAL `subscription` DAY)) AS `Subscription Ends`
FROM `user_table`
# Answer 2
First you calulate the the date, when the package will expire. (This can be done with the DateTime class of PHP if needed) Then you can simply compare it with time()
$date = new DateTime('2014-01-01'); //purchase date
$date->add(new DateInterval('P30D')); //in this case for 30 days
if(time() < $date->getTimestamp())
{
//expired
}
# Answer 3
Use the Unix Timestamp. It stores the current date as a number. Specifically, the numbers of seconds it has been since the Unix Epoch.
When they redeem the code, store the current Unix Timestamp. When you check, subtract the stored Unix Timestamp from the current Unix Timestamp. If it's over 24 * # of Days Valid, deactivate the code.
Store It
$date = time();
?>
Check It
$validNumDays = 30;
$validNumSeconds = $validNumDays * 60 * 60 * 24;
$dbtime = GetStoredTimestamp();
$diff = time() - $dbtime;
if($diff > $validNumSeconds) { Deactivate(); }
?>