Page 1 of 1

Trying to compare two dates…one from mysql DB stored as DATE

Posted: Thu Oct 31, 2013 10:54 pm
by 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:

Code: Select all

<?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();				

	?>

Re: Trying to compare two dates…one from mysql DB stored as

Posted: Fri Nov 01, 2013 1:25 am
by pbs
convert the dates in unix timestamp and then compare, use mktime() function

Re: Trying to compare two dates…one from mysql DB stored as

Posted: Fri Nov 01, 2013 6:18 am
by Celauran

Re: Trying to compare two dates…one from mysql DB stored as

Posted: Fri Nov 01, 2013 11:00 pm
by ndjustin20
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:

Code: Select all

<?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();				

	?>