A more elegant way of comparing dates from MySQL database

[jstam]

I am trying to compare a date in a mysql database with the current date. If the date has been passed, then the program does something.

My program runs successfully, but I'm pretty sure that there's a more elegant solution out there. I think mine is quite possibly the most brute force solution out there 🙂

$query = "SELECT title, titleurl, organizer, organizerurl, DATE_FORMAT(start, '%b %D, %Y'), DATE_FORMAT(end, '%b %D, %Y'), place, DATE_FORMAT(paperdeadline, '%b %D, %Y'), month(paperdeadline), dayofmonth(paperdeadline), year(paperdeadline) FROM conferences ORDER BY start";
$result = mysql_query($query, $db) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {

// I've erased the part where I assign variables to irrelevant table variables

$now = getdate();
if ($now['year']>$deadliney || ($now['year']==$deadliney && $now['mon']>$deadlinem) || ($now['year']==$deadliney && $now['mon'] == $deadlinem && $now['mday']>$deadlined))
					$paperdeadline="<font color=\"#FF0000\">$paperdeadline (Deadline Passed)</font>";				
}

[TheDefender]

Use UNIX Timestamps to actually make your comparisons. It is much easier than trying to compare years, months and days like that.

$now = time(); // Outputs the number of seconds since the Epoch

When you query the table, extract your date field in a UNIX TIMESTAMP format, which gives you a value in the number of seconds since the Epoch.

Then just compare them:

IF($now > $then){
// Do this
} else {
// Do this instead
}

I have found that it is always easiest to manipulate your dates, and compare them, etc. when using the UNIX Timestamp format rather than YYYY-MM-DD, or what have you...