Janus13 Posted August 6, 2006 Share Posted August 6, 2006 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] Quote Link to comment Share on other sites More sharing options...
Barand Posted August 6, 2006 Share Posted August 6, 2006 You may find it easier to work with days leftSELECT *, (TO_DAYS(expiration) - TO_DAYS(CURDATE)) as daysToExpiry FROM ... Quote Link to comment Share on other sites More sharing options...
Janus13 Posted August 7, 2006 Author Share Posted August 7, 2006 [quote author=Barand link=topic=103201.msg410860#msg410860 date=1154893876]You may find it easier to work with days leftSELECT *, (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 tableinstead of select * from table where field between blah and blah ? Quote Link to comment Share on other sites More sharing options...
Janus13 Posted August 7, 2006 Author Share Posted August 7, 2006 That query seems to always bring back every row rather than the range I'm looking for. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 7, 2006 Share Posted August 7, 2006 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")); Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.