Jump to content


Photo

Date Calculation


  • Please log in to reply
4 replies to this topic

#1 Janus13

Janus13
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 06 August 2006 - 07:00 PM

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!

<?
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.
		}
	}	
}
?>


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 06 August 2006 - 07:51 PM

You may find it easier to work with days left

SELECT *, (TO_DAYS(expiration) - TO_DAYS(CURDATE)) as daysToExpiry FROM ...
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Janus13

Janus13
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 07 August 2006 - 12:37 AM

You may find it easier to work with days left

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

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 ?

#4 Janus13

Janus13
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 07 August 2006 - 01:48 AM

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

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 07 August 2006 - 07:08 AM

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"));
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users