Trying to compare two dates…one from mysql DB stored as DATE and one using DateTime object

[ndjustin20]

I have been trying unsuccessfully to compare two dates using an if statement to then run another query and update a mysql database. I'm not sure where I am going wrong. If I use procedural and use

 

date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date30)));

 

Then I get an update of every field in the column boosterWithinDays.

 

If I try and use DateTime object

 

$boosterDate = new DateTime($row['boosterDate']);

$boosterDate->format("Y-m-d");

 

Then I get no update at all.  I've set some boosterDates to more than 30 days and some to less and nothing seems to work.

 

Here is the complete code:

<?php
	//date_default_timezone_set('America/Chicago');
	$todaysDate = new DateTime('now');
	$formattedDate = $todaysDate->format('Y-m-d');
	
	$date = new DateTime ('now');
	$date90 = $date->add(new DateInterval('P90D'));
	$date90 = $date->format('Y-m-d');
	
	$date = new DateTime ('now');
	$date60 = $date->add(new DateInterval('P60D'));
	$date60 = $date->format('Y-m-d');
	
	$date = new DateTime ('now');
	$date30 = $date->add(new DateInterval('P30D'));
	//$date30 = $date->format('Y-m-d');
	
	//echo $date90;
	//echo "<br />";
	//echo $formattedDate;

		$sql = "SELECT * FROM service;";

	if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}
		
	while($row = $result->fetch_array()){
			//Get all the rows and store them in an array	
		$firstQueryRows[] = $row;
		
	}
	
	foreach($firstQueryRows as $row){
		//do a new query with $row
		
		$patientID = $row['patientID'];
		$serviceID = $row['serviceID'];
		
		//$boosterDate = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $row['boosterDate'])));
		//$date30 = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date30)));
		//$date60 = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date60)));
		//$date90 = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date90)));
		
		$boosterDate = new DateTime($row['boosterDate']);
		//$boosterDate->format("Y-m-d");
		
		
		if($boosterDate < $date30){
				//echo "The date is less than 30" . " " . $row['serviceID'] . "<br /><br />";
				//echo $date30 . "<br /><br />";
				//echo $boosterDate;
				
			$sql = "UPDATE service SET boosterWithinDays = 30;";
		if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}
		
	}
	}
			
			//$date = $row['boosterDate'];
			//$id = $row['patientID'];			
			//echo $date . " " . $id . "<br /><br />";
		//echo date_default_timezone_get();				

	?>

[PravinS]

you cannot compare dates in "Y-m-d" format, you need to convert it into timestamp format, use mktime() function

[Barand]

you cannot compare dates in "Y-m-d" format, you need to convert it into timestamp format, use mktime() function

[ndjustin20]

Barand I actually laughed out loud when I saw your post...nice work.  From the php.net site http://ca3.php.net/manual/en/datetime.diff.php you can do this.

 

$date1 = new DateTime($date);

$date2 = new DateTime($date);

 

Then from that point you can use a comparison operator for the two var's like <, >, or ==.  For some reason this isn't working in my code.  No matter true or false my field boosterWithinDays alwasy updates to 30.  I have no idea why the query runs when it's false.  Any help is much appreciated.

 

Here is my code currently:

<?php
	//date_default_timezone_set('America/Chicago');
	$todaysDate = new DateTime('now');
	$formattedDate = $todaysDate->format('Y-m-d');
	
	$date = new DateTime ('now');
	$date90 = $date->add(new DateInterval('P90D'));
	$date90 = $date->format('Y-m-d');
	
	$date = new DateTime ('now');
	$date60 = $date->add(new DateInterval('P60D'));
	$date60 = $date->format('Y-m-d');
	
	$date = new DateTime ('now');
	$date30 = $date->add(new DateInterval('P30D'));
	$date30 = $date->format('Y-m-d');
	
	//echo $date90;
	//echo "<br />";
	//echo $formattedDate;

		$sql = "SELECT * FROM service;";

	if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}
		
	while($row = $result->fetch_array()){
			//Get all the rows and store them in an array	
		$firstQueryRows[] = $row;
		
	}
	
	foreach($firstQueryRows as $row){
		//do a new query with $row
		
		$patientID = $row['patientID'];
		$serviceID = $row['serviceID'];
		
		//$boosterDate = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $row['boosterDate'])));
		//$date30 = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date30)));
		//$date60 = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date60)));
		//$date90 = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $date90)));
		
		$boosterDate = new DateTime($row['boosterDate']);
		//$boosterDate->format("Y-m-d");
		//$interval = $boosterDate->diff($date30);
		$boosterDate = $boosterDate->format("Y-m-d");
		var_dump($boosterDate > $date30);
		
		echo "<br /><br />";
		print "This is the booster date" . " " . ($boosterDate) . "  ";
		echo "<br /><br />";
		print "This is todays date plus 30 days" . " " . ($date30) . "  ";
		
		if($boosterDate < $date30){
				//echo "The date is less than 30" . " " . $row['serviceID'] . "<br /><br />";
				//echo $date30 . "<br /><br />";
				//echo $boosterDate;
			echo "this is being shown because boosterDate object is less than date30 object";
			$sql = "UPDATE service SET boosterWithinDays = 30;";
		if(!$result = $con->query($sql)){
		die('There was an error running the query [' . $con->error . ']');	
		}
		
	}else{
		NULL;
	}
	}
			
			//$date = $row['boosterDate'];
			//$id = $row['patientID'];			
			//echo $date . " " . $id . "<br /><br />";
		//echo date_default_timezone_get();				

	?>

[Barand]

If I am correct about your goal then a single query should do it.

 

As a test I created a file of dates from 10 days ago to 100 days in the future and with a boosterWithinDays field (all NULL)

UPDATE dates SET boosterwithindays =
CASE
    WHEN DATE(thedate) < CURDATE() THEN NULL
    WHEN DATE(thedate) < CURDATE()+INTERVAL 30 DAY THEN 30
    WHEN DATE(thedate) < CURDATE()+INTERVAL 60 DAY THEN 60
    WHEN DATE(thedate) < CURDATE()+INTERVAL 90 DAY THEN 90
    ELSE NULL
END

The resulting table looked like this:

 

 

thedate        age    boosterwithindays

2013-10-23    10    NULL
2013-10-24    9    NULL
2013-10-25    8    NULL
2013-10-26    7    NULL
2013-10-27    6    NULL
2013-10-28    5    NULL
2013-10-29    4    NULL
2013-10-30    3    NULL
2013-10-31    2    NULL
2013-11-01    1    NULL
2013-11-02    0    30
2013-11-03    -1    30
2013-11-04    -2    30
2013-11-05    -3    30
2013-11-06    -4    30
2013-11-07    -5    30
2013-11-08    -6    30
2013-11-09    -7    30
2013-11-10    -8    30
2013-11-11    -9    30
2013-11-12    -10    30
2013-11-13    -11    30
2013-11-14    -12    30
2013-11-15    -13    30
2013-11-16    -14    30
2013-11-17    -15    30
2013-11-18    -16    30
2013-11-19    -17    30
2013-11-20    -18    30
2013-11-21    -19    30
2013-11-22    -20    30
2013-11-24    -22    30
2013-11-26    -24    30
2013-11-30    -28    30
2013-12-01    -29    30
2013-12-03    -31    60
2013-12-04    -32    60
2013-12-05    -33    60
2013-12-06    -34    60
2013-12-07    -35    60
2013-12-08    -36    60
2013-12-09    -37    60
2013-12-10    -38    60
2013-12-11    -39    60
2013-12-12    -40    60
2013-12-13    -41    60
2013-12-14    -42    60
2013-12-15    -43    60
2013-12-16    -44    60
2013-12-17    -45    60
2013-12-18    -46    60
2013-12-19    -47    60
2013-12-20    -48    60
2013-12-21    -49    60
2013-12-22    -50    60
2013-12-23    -51    60
2013-12-24    -52    60
2013-12-25    -53    60
2013-12-26    -54    60
2013-12-27    -55    60
2013-12-28    -56    60
2013-12-29    -57    60
2013-12-30    -58    60
2013-12-31    -59    60
2014-01-01    -60    90
2014-01-02    -61    90
2014-01-03    -62    90
2014-01-04    -63    90
2014-01-05    -64    90
2014-01-06    -65    90
2014-01-07    -66    90
2014-01-08    -67    90
2014-01-09    -68    90
2014-01-10    -69    90
2014-01-11    -70    90
2014-01-12    -71    90
2014-01-13    -72    90
2014-01-14    -73    90
2014-01-15    -74    90
2014-01-16    -75    90
2014-01-17    -76    90
2014-01-18    -77    90
2014-01-19    -78    90
2014-01-20    -79    90
2014-01-21    -80    90
2014-01-22    -81    90
2014-01-23    -82    90
2014-01-24    -83    90
2014-01-25    -84    90
2014-01-26    -85    90
2014-01-27    -86    90
2014-01-28    -87    90
2014-01-29    -88    90
2014-01-30    -89    90
2014-01-31    -90    NULL
2014-02-01    -91    NULL
2014-02-02    -92    NULL
2014-02-03    -93    NULL
2014-02-04    -94    NULL
2014-02-05    -95    NULL
2014-02-06    -96    NULL
2014-02-07    -97    NULL
2014-02-08    -98    NULL
2014-02-09    -99    NULL

 

 

 

However, why would you need store the calculated result? You can just query the table to get those due in the next 30 days or whenever or calc the result when outputting the list

Edited November 2, 2013 by Barand

[ndjustin20]

I was trying to do exactly that.  I didn't want to store the 30, 60, or 90 days but I'm not able to get the DateTime object to work properly so i figured maybe just store the interval either within 30, 60, or 90 days then query that field and respond accordingly.

 

Really what I am trying to do is use the DateTime object as I'm not very familiar with oop but it doesn't seem to be working as described in the manual. 

 

Could you have a look at my code and see if you can see something I am doing wrong?  I am using php 5.4.12 and mysql 5.6.12 both loaded from a WAMP stack on a windows 7 64 bit machine.

[Barand]

I cannot see anything wrong with your use of the DateTime objects other than it unnecessary in the case of the date from the service records as that will be in Y-m-d format any way. If you are doing that to lose the time element of a datetime type column then it easier to "SELECT DATE(boosterdate) as boosterdate" to just get the date part.

 

Your update query has no WHERE clause and so will set the value to 30 in every record in the table

 

 

$sql = "UPDATE service SET boosterWithinDays = 30;";

 

Also you don't use the terminating ";" (the one after 30) when running queries from PHP.

 

To get the service recs where boosterdate is within the next 30 days you can either use the DateTime class to calc the dates

SELECT * FROM service WHERE DATE(boosterdate) BETWEEN '$formattedDate' AND '$date30'

or do entirely in SQL (easier IMHO)

SELECT * FROM service WHERE DATE(boosterdate) BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY

Edited November 2, 2013 by Barand

[ndjustin20]

Barand thank you so much for your help.  I shouldn't need a where clause though as the query should only run if the date is less than $date30 which is todays date plus 30 days.  The problem I am having is the code is running regardless of true or false.  I have output the boolean using var_dump to show that I am getting true and false back yet the if statement continues to run regardless.  Is there maybe something buggy with it?

 

I really LOVE your solution.  So much easier!!!  I was hoping to use the new DateTime object but I think I'll resign to the fact that it's just not working right at this point.  Lets say I don't know a whole bunch of sql :-) except the basics.  Do you have a good online tutorial that may give some good examples that you have maybe used in the past to brush up on things like between and curdate ect?

 

I found this http://dev.mysql.com/doc/refman/5.6/en/func-op-summary-ref.html and that should do the trick for more information on sql queries. 

Edited November 2, 2013 by ndjustin20