Date Calculation

[Janus13]

I have a strange problem with the below code.  The code is supposed to look at a date value in a sql table and compare it to today's date.  If the date is before the limit value then nothing happens, if it is at the 14 day (or whatever value that is set to) then it should set the value of expire_reminder to 1 and generate an email (email part stripped).  If it's 7 days before the expiration it sends another notice out, then when the value is beyond the limit then it expires the user.  All of the expiration code, and email works fine, but it's the date calculation that doesn't work.  I've seen it lately expire the day the person gets added to the site, so I would like someone to look at the below and see if I'm calculating the date wrong below.  Thanks in advance!

[code]
<?
include '/includes/sql.php';
include '/includes/mail.php';
$days_until = '14';
$limit = date('Y-m-d', strtotime("+$days_until days", strtotime("now")));
$now = date('Y-m-d');
$query = "SELECT * FROM table WHERE expiration BETWEEN '$now' AND '$limit'";
$result = $db->dbQuery($query);
mail_setup();
if(mysql_num_rows($result))
{
while ($row = mysql_fetch_assoc($result))
{
$userid = $row['userid'];
$expirationDate = $row['expiration'];
$username = $row['username'];
$emailAddress = $row['email_address'];
$firstName = $row['first_name'];
$expireReminder = $row['expire_reminder'];
$expirationDate = $row['expiration'];
$adminRemind = $row['admin_remind'];
$accessLevel = $row['access_level'];
$new_time = explode('-', $expirationDate);

$timeleft = daysleft($new_time[0],$new_time[1],$new_time[2],0,0);

// Email to users who are expiring if it hasn't been sent already
if($expireReminder == '0' && $expirationDate != '0000-00-00')
{
//email code stripped out
$updateQuery = "UPDATE table SET expire_reminder = '1' WHERE userid='$userid'";
mysql_query($updateQuery);
}
else if($expireReminder == '1' && ($days_left <= '7' || $days_left >= '1'))
{
$updateQuery = "UPDATE table SET expire_reminder = '2', admin_remind = '1' WHERE userid = '$userid'";
mysql_query($updateQuery) or die(mysql_error());
//email code stripped out
}
//in theory if all reminders have been sent out this should now set them activated.
else if($expireReminder == '2' && $expirationDate == $now && $adminRemind != '1')
{
//set activated to 3 i.e. disabled
$updateQuery = "UPDATE table SET activated = '3', admin_remind = '1' WHERE userid = '$userid'";
mysql_query($updateQuery);
//email code striped out.
}
}
}
?>
[/code]

[Barand]

You may find it easier to work with days left

SELECT *, (TO_DAYS(expiration) - TO_DAYS(CURDATE)) as daysToExpiry FROM ...

[Janus13]

[quote author=Barand link=topic=103201.msg410860#msg410860 date=1154893876]
You may find it easier to work with days left

SELECT *, (TO_DAYS(expiration) - TO_DAYS(CURDATE)) as daysToExpiry FROM ...
[/quote]
That's a new format for a sql statement so let me make sure I have it. 

Do select *, (TO_DAYS(expiration) - TO_DAYS(CURDATE)) as daysToExpire from table

instead of select * from table where field between blah and blah ?

[Janus13]

That query seems to always bring back every row rather than the range I'm looking for.

[Barand]

You stll need the remainder of your query. I was just showing you how to get MySql to calculate the days for you.

And

$limit = date('Y-m-d', strtotime("+$days_until days", strtotime("now")));

should be

$limit = date('Y-m-d', strtotime("+$days_until days"));