Jump to content

Archived

This topic is now archived and is closed to further replies.

Janus13

Date Calculation

Recommended Posts

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]

Share this post


Link to post
Share on other sites
You may find it easier to work with days left

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

Share this post


Link to post
Share on other sites
[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 ?

Share this post


Link to post
Share on other sites
That query seems to always bring back every row rather than the range I'm looking for.

Share this post


Link to post
Share on other sites
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"));

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.