• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

Cron Job PHP (int)Difference issue

Hi everyone,

I have a cron job script that checks if a user has had activity for a given amount of days and if not it sends them a reminder email. The email part of the script is working correct when the user sets how frequent they would like reminders but it is sending a reminder email regardless of whether or not they have activity which is not correct. I've tested both the SQL statements individually and they work correctly.

Here is the part of the script that is checking this:

$query = mysql_query("SELECT * FROM phpfox_user WHERE remind_j = '1' ");
	
	$users_to_remind =  mysql_num_rows($query);
	
	if ( $users_to_remind > 0 )
	{
		while ( $user = mysql_fetch_array($query) )
		{
			// Grabbing the username
			$username = $user["full_name"];
			
			// Grabbing the number of days after which they want to be reminded
			$days =  (int)$user["remindafter_j"];
			
			// Grabbing the email address of the uesr
			$email = $user["email"];
			
			$query2 = mysql_query("SELECT * FROM journal_table WHERE user_id = '$username' ORDER BY entry_timestamp DESC LIMIT 1");
			$row2 = mysql_fetch_array($query2);
			
			// Grabbing the latest meditation logged
			$latest = $row2["entry_timestamp"];
			
			// How many days the user hasn't medicated
			$not_medicated = (int)Difference($latest);
			
			//Split username into first name
			$pieces = explode(" ", $username );

			// echo $pieces[0]; // First name
			
			// Checking if they should be reminded or not
			if ( $not_medicated >= $days )
			{
				//Sends Reminder Email
			}

Open in new window


Thanks for your help,

D
0
TLN_CANADA
Asked:
TLN_CANADA
  • 18
  • 17
  • 4
2 Solutions
 
Robert SchuttSoftware EngineerCommented:
As before, it seems suspicious that you compare user_id to $username which holds a full name, but I can't be sure that's wrong.

Have you echo'ed $not_medicated and Difference($latest) for a specific case?
0
 
Robert SchuttSoftware EngineerCommented:
Sorry, I meant echo the variables $not_medicated and $days...
0
 
Robert SchuttSoftware EngineerCommented:
By the way, you should test whether $row2 contains a record, even though it may only be a theoretical possibility that it's empty.

I just tested the Difference function you posted on an earlier question and it checks out.
<?php
$days = 5;
$latest = date('Y-m-d H:i:s', time() - 60 * 60 * 24 * 7); // 7 days ago, try 4 to skip mail
$not_medicated = (int)Difference($latest);
echo $not_medicated.'<br>';
if ( $not_medicated >= $days )
{
	echo 'Sends Reminder Email<br>';
}
else
{
	echo "No mail to be sent...<br>";
}

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TLN_CANADAAuthor Commented:
Thanks Robert, I will test this out and get back to you shortly.
0
 
TLN_CANADAAuthor Commented:
Hi Robert, I'm not clear on this. Do you want me to replace the $latest variable with your call? I see no call to the DB in this.  

I will then print the variables for you.

Thanks,

Derek
0
 
Robert SchuttSoftware EngineerCommented:
No that was just a test. It succeeded. So yes, I can only think that the variables hold different values than you assume.
0
 
TLN_CANADAAuthor Commented:
Okay thanks, so can you show me how to integrate your code above with the original I sent you as I'm not sure how to intergate the $latest line you have.
0
 
Robert SchuttSoftware EngineerCommented:
No no, that was just a test. Your code seems fine, but I can only suspect that those 2 variables don't hold the values you assume they do. That could be because the database is not returning the values you assume it does. You say you tested the queries but did you echo the queries from the code or did you just test the queries as you assumed they would be executed from the code?

Just to check this off, try to make sure every step does what you expect:
if ($row2 = mysql_fetch_array($query2)) { echo 'ok!'; } else { echo 'problem!'; }

Open in new window


and from my earlier test script: instead of a comment, show what's going on:
if ( $not_medicated >= $days )
{
	echo 'Sends Reminder Email<br>';
}
else
{
	echo "No mail to be sent...<br>";
} 

Open in new window

0
 
Ray PaseurCommented:
Questions about the data can usually be resolved by looking at the data.  The PHP function var_dump() can help.  After you use var_dump() you must use your browser View Source to examine the contents of the data.

In my experience as a professional developer and teacher, I have found that you cannot over-use var_dump()!
0
 
TLN_CANADAAuthor Commented:
Thanks Ray for this.
0
 
TLN_CANADAAuthor Commented:
Robert, I'm still looking into this and will post in a couple of hours.
0
 
TLN_CANADAAuthor Commented:
I'm guessing it has something to do with the way the functions are working out what a day is. I used var dump a few minutes ago and here is what was printed:

Not Medicated Variable
int(1) 
Days variable
int(1) 
Username variable
string(13) "Derek" 
Latest Variable
string(19) "2013-01-29 16:53:50" 

Open in new window


You can see that the latest record is in the last 24 hours so it should not have sent an email.

Here is the entire code for the day including how it works out the time functions as it probably has something to do with this.

<?php

	function Difference($created_time)
    {
		date_default_timezone_set('America/Vancouver');
	
        $str = strtotime($created_time);
        $today = strtotime(date('Y-m-d H:i:s'));
        
        // It returns the time difference in Seconds...
        $time_differnce = $today-$str;
        
        // To Calculate the time difference in Years...
        $years = 60*60*24*365;
        
        // To Calculate the time difference in Months...
        $months = 60*60*24*30;
        
        // To Calculate the time difference in Days...
        $days = 60*60*24;
        
        // To Calculate the time difference in Hours...
        $hours = 60*60;
        
        // To Calculate the time difference in Minutes...
        $minutes = 60;

        if(intval($time_differnce/$years) > 1)
        {
            return (int)($time_differnce/$years * 365);
        }else if(intval($time_differnce/$years) > 0)
        {
            return (int)($time_differnce/$years * 365);
        }else if(intval($time_differnce/$months) > 1)
        {
            return (int)(($time_differnce/$months) * 30);
        }else if(intval(($time_differnce/$months)) > 0)
        {
            return (int)(($time_differnce/$months) * 30);
        }else if(intval(($time_differnce/$days)) > 1)
        {
            return intval(($time_differnce/$days));
        }else if (intval(($time_differnce/$days)) > 0) 
        {
            return intval(($time_differnce/$days));
        }else if (intval(($time_differnce/$hours)) > 1) 
        {
            return 1;
        }else if (intval(($time_differnce/$hours)) > 0) 
        {
            return 1;
        }else if (intval(($time_differnce/$minutes)) > 1) 
        {
            return 1;
        }else if (intval(($time_differnce/$minutes)) > 0) 
        {
            return 1;
        }else if (intval(($time_differnce)) > 1) 
        {
            return 1;
        }else
        {
            return 1;
        }
    }
	
?>

<?php
	
	mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );

	$query = mysql_query("SELECT * FROM phpfox_user WHERE remind_j = '1' ");
	
	$users_to_remind =  mysql_num_rows($query);
	
	if ( $users_to_remind > 0 )
	{
		while ( $user = mysql_fetch_array($query) )
		{
			// Grabbing the username
			$username = $user["full_name"];
			
			// Grabbing the number of days after which they want to be reminded
			$days =  (int)$user["remindafter_j"];
			
			// Grabbing the email address of the uesr
			$email = $user["email"];
			
			$query2 = mysql_query("SELECT * FROM journal_table WHERE user_id = '$username' ORDER BY entry_timestamp DESC LIMIT 1");
			$row2 = mysql_fetch_array($query2);
			
			// Grabbing the latest meditation logged
			$latest = $row2["entry_timestamp"];
			
			// How many days the user hasn't medicated
			$not_medicated = (int)Difference($latest);
			
			//Split username into first name
			$pieces = explode(" ", $username );
			
			echo "Not Medicated Variable" . "<br />";
			var_dump($not_medicated) ;
			echo "<br />";
			 echo "Days variable" . "<br />";
			 var_dump($days) ;
			 echo "<br />";
			 echo "Username variable" . "<br />";
			var_dump($username) ;
			 echo "<br />";
			 echo "Latest Variable" . "<br />";
			var_dump($latest) ;
			 echo "<br />";

			// echo $pieces[0]; // First name
			
			// Checking if they should be reminded or not
			if ( $not_medicated >= $days )
			{
				// Preparing the e-mail
				$to = $email;
				$subject = "$pieces[0], It's time to Journal";
				$message = "Greetings $pieces[0], \n\n As you'd asked us before, we're reminding you that you have not logged any journal entries in $days day" . ((1 == $days) ? '' : 's') . ". \n\n  Hope to see you soon! \n\n Clearthemirror.com Team \n\n \n\n  If you would like to turn off these reminders you can by visiting \n\n http://www.clearthemirror.com/clearthemirror/jourstats/";
				$headers = "From: noreply@clearthemirror.com";
				
				// Sending the e-mail
				mail($to,$subject,$message,$headers) or die ("Something went wrong sending the e-mail!");
			}
			
		}
	}

?>

Open in new window


Thanks again,

D
0
 
Robert SchuttSoftware EngineerCommented:
With both those integer variables equal to 1, this if expression:
if ( $not_medicated >= $days )

Open in new window

will be true and the mail will be sent.

The version of the Difference function I had returned 0 for values too low. In this version it returns 1. The problem seems to be in that function because it should be returning 7. If you have php version 5.3.0 or better than you can use date_diff.
0
 
Robert SchuttSoftware EngineerCommented:
Short version (no error checking at all) would be:
 function Difference($created_time)
    {
		return date_diff(new DateTime('now'), new DateTime(substr($created_time, 0, 10)))->format("%a");
    }

Open in new window

0
 
TLN_CANADAAuthor Commented:
Thank you Robert, yes I have this PHP version. So how would I insert this function into the code?
0
 
Robert SchuttSoftware EngineerCommented:
For the moment I would say just replace the existing version of it.
0
 
TLN_CANADAAuthor Commented:
Okay, thanks Robert, I'll give it a try.
0
 
TLN_CANADAAuthor Commented:
Thank you so much! I think it's working correctly now. I will test it some more and get back to you.
0
 
TLN_CANADAAuthor Commented:
I've tested it, it works well for more than one day but I don't think it's working correct for just over the 1 day mark.

For example, if I set the reminder days to 1 and have a logged in the database from yesterday at 7pm. If the script runs at 5pm today (that 22 hours) it is still sending a reminder email.

Is there an alteration we can make to this so that it's for a 24 difference that the script checks?

Thanks,

D
0
 
Robert SchuttSoftware EngineerCommented:
Yeah, that's because the time is stripped, I shouldn't have done that, the time of running the script affects the check whether the interval is 1 day or more. There would have to be a separate check for the time when the interval in days is exactly the wanted maximum but I imagine the code being quite awful. Alternatively, you could ditch the Difference function and use a DateInterval to calculate the date/time you want to use for checking:
			// Grabbing the latest meditation logged
			$latest = $row2["entry_timestamp"];
			// some tests:
			//$latest = "2013-01-29 16:53:50";
			//$latest = new DateTime($latest);

			// How many days the user hasn't medicated
			//$not_medicated = (int)Difference($latest);
			
			//Split username into first name
//			$pieces = explode(" ", $username );

			// echo $pieces[0]; // First name

			$checkdate = new DateTime('now');
			$checkdate->sub(New DateInterval('P'.$days.'D')); // check point, if $latest is before this, then send the mail

			echo "'checkdate' Variable" . "<br />";
			var_dump($checkdate) ;
			echo "<br />";

			// Checking if they should be reminded or not
			//if ( $not_medicated >= $days )
			if ( $latest < $checkdate )
			{
				echo 'Sends Reminder Email<br>';
			}
			else
			{
				echo "No mail to be sent...<br>";
			}


			//echo "Not Medicated Variable" . "<br />";
			//var_dump($not_medicated) ;
			//echo "<br />";
			 echo "Days variable" . "<br />";
			var_dump($days) ;
			 echo "<br />";
			 echo "Username variable" . "<br />";
			var_dump($username) ;
			 echo "<br />";
			 echo "Latest Variable" . "<br />";
			var_dump($latest) ;
			 echo "<br />";

Open in new window

The timestamp from the database should already be ok, but if not, put line 5 back in (uncomment it) to convert it so the comparison will work.
0
 
Ray PaseurCommented:
This article might also provide some useful information.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Practical Application #2 and #3 may give some useful guidance.
0
 
TLN_CANADAAuthor Commented:
Hey Robert,

Here are the results being printed on the screen for this, unfortunately it is still sending emails even when a record has been created in the last 24 hours.

'checkdate' Variable
object(DateTime)#4 (3) { ["date"]=> string(19) "2013-01-31 12:56:48" ["timezone_type"]=> int(3) ["timezone"]=> string(15) "America/Chicago" } 
Not Medicated Variable
int(0) 
Days variable
int(1) 
Username variable
string(13) "Derek Sheahan" 
Latest Variable
object(DateTime)#1 (3) { ["date"]=> string(19) "2013-02-01 10:54:57" ["timezone_type"]=> int(3) ["timezone"]=> string(15) "America/Chicago" } 

Open in new window


<?php

	 function Difference($created_time)
    {
		return date_diff(new DateTime('now'), new DateTime(substr($created_time, 0, 10)))->format("%a");
    }
	
?>

<?php
	
	mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );

	$query = mysql_query("SELECT * FROM phpfox_user WHERE remind_j = '1' ");
	
	$users_to_remind =  mysql_num_rows($query);
	
	if ( $users_to_remind > 0 )
	{
		while ( $user = mysql_fetch_array($query) )
		{
			// Grabbing the username
			$username = $user["full_name"];
			
			// Grabbing the number of days after which they want to be reminded
			$days =  (int)$user["remindafter_j"];
			
			// Grabbing the email address of the uesr
			$email = $user["email"];
			
			$query2 = mysql_query("SELECT * FROM journal_table WHERE user_id = '$username' ORDER BY entry_timestamp DESC LIMIT 1");
			$row2 = mysql_fetch_array($query2);
			
			// Grabbing the latest meditation logged
			$latest = $row2["entry_timestamp"];
			
			
			// How many days the user hasn't medicated
			$not_medicated = (int)Difference($latest);
			
			//Split username into first name
			$pieces = explode(" ", $username );
			
			
			$checkdate = new DateTime('now');
			$checkdate->sub(New DateInterval('P'.$days.'D')); // check point, if $latest is before this, then send the mail

			echo "'checkdate' Variable" . "<br />";
			var_dump($checkdate) ;
			echo "<br />";
			
			echo "Not Medicated Variable" . "<br />";
			var_dump($not_medicated) ;
			echo "<br />";
			 echo "Days variable" . "<br />";
			 var_dump($days) ;
			 echo "<br />";
			 echo "Username variable" . "<br />";
			var_dump($username) ;
			 echo "<br />";
			 echo "Latest Variable" . "<br />";
			var_dump($latest) ;
			 echo "<br />";

			// echo $pieces[0]; // First name
			
			// Checking if they should be reminded or not
			if ( $latest < $checkdate )
			{
				// Preparing the e-mail
				$to = $email;
				$subject = "$pieces[0], It's time to Journal";
				$message = "Greetings $pieces[0], ";
				$headers = "From: noreply@clear.com";
				
				// Sending the e-mail
				mail($to,$subject,$message,$headers) or die ("Something went wrong sending the e-mail!");
			}
			
		}
	}

?>

Open in new window


I tried it with and without this line:

$latest = new DateTime($latest);

and with it, it gives the error:

Warning: substr() expects parameter 1 to be string, object given in /home/clear555/public_html/jreminder.php on line 5

Thanks for your help,

D
0
 
Robert SchuttSoftware EngineerCommented:
that error seems more connected to line 40, where you call the Difference function. Can you take that function and all lines containing $not_medicated out for the moment?

looking at the vardumps, "latest < checkdate" is definitely false, so this should work now... Please double check and do yourself a favour, put an else part in (with a simple echo 'no mail sent';) so it's 100% clear when no mail is being sent.
0
 
Ray PaseurCommented:
Maybe I am missing something, but it seems easy to do this if the difference can be expressed in the English language as a number of days, weeks, hours, etc.  Please see:
http://www.laprbass.com/RAY_temp_tln_canada.php

<?php // RAY_temp_tln_canada.php
error_reporting(E_ALL);
date_default_timezone_set('America/Chicago');

$alpha = date('r');
$omega = date('r', strtotime('NOW - 1 DAY'));
echo "<br/>IT IS NOW $alpha AND ONE DAY AGO IT WAS $omega";

$days  = 3;
$alpha = 'January 15, 2013 2:25pm';
$omega = $alpha . " + $days DAY";
$a = date('r', strtotime($alpha));
$z = date('r', strtotime($omega));
echo "<br/>FROM $a TO $z IT IS EXACTLY $days DAYS";

Open in new window

0
 
Robert SchuttSoftware EngineerCommented:
Ray, that's what the code on line 46-47 is supposed to do: subtract $days days from today. Did I do something wrong?
0
 
Ray PaseurCommented:
I don't think you did anything wrong, but I'm predisposed to data-driven testing.  When I looked at the Difference() function it seemed to be a lot of code that would be right some, but maybe not all of the time, and PHP has built-in functions for all kinds of date calculations.

Probably worth noting that PHP and MySQL may have different clocks and different timezone settings.  I would check that if there are still any anomalous outputs.
0
 
TLN_CANADAAuthor Commented:
Thank you both.

I changed the timeszone to Vancouver as this is what the database is saved as but this is only a couple of hours from Chicago so I don't think this is what is causing the error at the moment.

Here is the test code:

'checkdate' Variable
object(DateTime)#1 (3) { ["date"]=> string(19) "2013-01-31 12:52:02" ["timezone_type"]=> int(3) ["timezone"]=> string(17) "America/Vancouver" } 
Not Medicated Variable
NULL 
Days variable
int(1) 
Username variable
string(13) "Derek" 
Latest Variable
string(19) "2013-02-01 10:54:57" 
'checkdate' Variable
object(DateTime)#2 (3) { ["date"]=> string(19) "2013-01-30 12:52:03" ["timezone_type"]=> int(3) ["timezone"]=> string(17) "America/Vancouver" } 

Open in new window


You can see from this that their was a record logged in the DB about 2 hours ago but it is still sending a reminder email wheresas it should only send if the user has not logged one for 24 hours.

Ray, as you had said Robert's code is correct I did not try your full function yet (just updated the timezone).

Robert, I've also added the else clause at the end of the script, thanks for reminding me as I had missed that.

Here is what I have:

<?php
	date_default_timezone_set('America/Vancouver');

	 function Difference($created_time)
    {
		return date_diff(new DateTime('now'), new DateTime(substr($created_time, 0, 10)))->format("%a");
    }
	
?>

<?php
	
	mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );

	$query = mysql_query("SELECT * FROM phpfox_user WHERE remind_j = '1' ");
	
	$users_to_remind =  mysql_num_rows($query);
	
	if ( $users_to_remind > 0 )
	{
		while ( $user = mysql_fetch_array($query) )
		{
			// Grabbing the username
			$username = $user["full_name"];
			
			// Grabbing the number of days after which they want to be reminded
			$days =  (int)$user["remindafter_j"];
			
			// Grabbing the email address of the uesr
			$email = $user["email"];
			
			$query2 = mysql_query("SELECT * FROM journal_table WHERE user_id = '$username' ORDER BY entry_timestamp DESC LIMIT 1");
			$row2 = mysql_fetch_array($query2);
			
			// Grabbing the latest meditation logged
			$latest = $row2["entry_timestamp"];
			//$latest = new DateTime($latest);
			
			// How many days the user hasn't medicated
			//$not_medicated = (int)Difference($latest);
			
			//Split username into first name
			$pieces = explode(" ", $username );
			
			
			$checkdate = new DateTime('now');
			$checkdate->sub(New DateInterval('P'.$days.'D')); // check point, if $latest is before this, then send the mail

			echo "'checkdate' Variable" . "<br />";
			var_dump($checkdate) ;
			echo "<br />";
			
			echo "Not Medicated Variable" . "<br />";
			var_dump($not_medicated) ;
			echo "<br />";
			 echo "Days variable" . "<br />";
			 var_dump($days) ;
			 echo "<br />";
			 echo "Username variable" . "<br />";
			var_dump($username) ;
			 echo "<br />";
			 echo "Latest Variable" . "<br />";
			var_dump($latest) ;
			 echo "<br />";

			// echo $pieces[0]; // First name
			
			// Checking if they should be reminded or not
			if ( $latest < $checkdate )
			{
				// Preparing the e-mail
				$to = $email;
				$subject = "$pieces[0], It's time to Journal";
				$message = "Greetings $pieces[0],";
				$headers = "From: noreply@clear.com";
				
				// Sending the e-mail
				mail($to,$subject,$message,$headers) or die ("Something went wrong sending the e-mail!");
			}
			else
			{
				echo "No mail to be sent...<br>";
			}
			
		}
	}

?>

Open in new window



Thank you both so much!
0
 
Robert SchuttSoftware EngineerCommented:
Now something is definitely wrong. Latest contains a string now and a second checkdate is written out after which the output ends? This doesn't add up.
0
 
TLN_CANADAAuthor Commented:
Sorry, the checkdate issue was my bad. I selected the first results of the array but accidentally included the first line of the second result too so this part is fine.
0
 
TLN_CANADAAuthor Commented:
The string part for the username variable is correct.
0
 
Robert SchuttSoftware EngineerCommented:
Ok, it could help if you put this line back in:
error_reporting(E_ALL);

Open in new window

Also, comment out the lines referencing $not_medicated (54-56).
Can you double check that the database field entry_timestamp is a date field?
0
 
Robert SchuttSoftware EngineerCommented:
Yes username is fine but this isn't:
Latest Variable
string(19) "2013-02-01 10:54:57" 

Open in new window

0
 
Robert SchuttSoftware EngineerCommented:
I think when the previous output you posted showed the correct content for 'Latest' so maybe line 38 was uncommented at that point. Remember the error you got was not related to that line as far as I could see and yet now I see it is commented out.
0
 
TLN_CANADAAuthor Commented:
Sorry Robert, I had this  field as a TIMESTAMP in the database, I should change this to date?
0
 
TLN_CANADAAuthor Commented:
I remember trying to change this before and when I do it gives the error:

Invalid default value.
0
 
Robert SchuttSoftware EngineerCommented:
No that should be fine. But I guess I thought that would be a date in php but it is a string so line 38 is needed back in.
0
 
TLN_CANADAAuthor Commented:
Awesome! I think we have it now! Give me another few minutes testing this. Honestly Robert , I can't thank you enough for your patience with this.
0
 
Robert SchuttSoftware EngineerCommented:
No sweat ;-)
0
 
TLN_CANADAAuthor Commented:
:)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 18
  • 17
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now