Comparing mysql dates in php

Question

Hi Guys.

I'm creating an application for a friend to handle Deadlines. I have a page set up where each user can see their own 'jobs to do' with a deadline next to it. My question is...

How do I compare a deadline date that comes back from the mysql query as 2010.08.08 with today's date?

For Example...

 <?php
while($row = mysql_fetch_array($result)){

$jobfinishdate = $row['jobfinishdate'];
$date = date('Y-m-d');

if ($jobfinishdate>$date)
 $jobfinishdate .= " - Not due yet!" ;
 else if ($jobfinishdate<$date)
  $jobfinishdate .= " - You didn't meet this deadline!";
  else if ($jobfinishdate==$date)
 $jobfinishdate .= " - Deadline is TODAY!";

} ?>

This works ok. But what I'd really like to do is display a message saying 'you have 5 days until deadline. Any ideas how to get around this?

Thanks.

Shane.

Answer 1

$days = (strtotime($jobfinishdate) - strtotime($date)) / (60 * 60 * 24);

Should get you the amount of days left on the deadline.

Edit: The above will always return the difference in days - to handle whether or not its before or beyond the due date, maybe (using just time() as Adam suggested):

$date_passed = false;
$days = strtotime($jobfinishdate) - time();
if ($days < 0) { $date_passed = true; }
$days = $days / (60 * 60 * 24);

if (!$date_passed)
{
  echo "You have $days days left on this project.";
}
else
{
  echo "Deadline has expired $days days ago.";
}

Answer 2

If you're using PHP 5.3.0 or newer you can use the DateTime object

Answer 3

// calculate days left
$daysleft = round( ( strtotime( $jobfinishdate ) - time() ) / 86400 );

// print out text for $daysleft
if( $daysleft == 0 )
   print( "Deadline is today!" );
else if ( $daysleft > 0 )
   printf( "You have %d days until deadline.", $daysleft );
else
   print( "You did not meet the deadline!" );

Answer 4

In SQL query:

SELECT
...,
TIMESTAMPDIFF(DAY, NOW(), `date_deadline`) AS days_to_deadline
...

This will produce positive number of days due deadline and negative for expired tasks.

Answer 5

If possible I would let the database return the number of days, with a query like this:

SELECT jobfinishdate, datediff(jobfinishdate, NOW() AS days) FROM table

Then use this number of days:

while ($row = mysql_fetch_array($result)){
    $jobfinishdate = $row['jobfinishdate'];
    $days = $row['days'];   

    if ($days > 0) {
        $jobfinishdate .= " - Not due yet! $days until deadline" ;
    } elseif ($days < 0) {
        $jobfinishdate .= " - You didn't meet this deadline!";
    } else {
        $jobfinishdate .= " - Deadline is TODAY!";
}

}

Some other remarks:

• If you keep the date calculation in PHP, move the $date declaration outside the while loop because you only need to do that once.
• you can remove the last condition, if ($jobfinishdate==$date). If the date is not larger and not smaller, it can only be equal.