Failing to compare php date with mysql date

Question

Got stuck in a complex or maybe stupid problem. I am getting a query from mysql, and then trying to compare a date column with a PHP data which i formatted to the same format i.e "Y-m-d" it always results in no match, although i see there is a match.. and it gets the right result set too.

<?php

date_default_timezone_set('America/Los_Angeles'); // set timezone to our timezone
$constantTime = time(); // get value of time in constant
$appDate = date("Y-m-d", $constantTime); //that defines php time variable - 
$queryDate = "SELECT * FROM date WHERE date='$appDate'";
$resultDate = mysql_query($queryDate) or die("Sorry Website Under Maintainence");
$recordDate = mysql_fetch_array($resulDate);
if ($appDate == date("Y-m-d", strtotime($recordDate['date']))) {
    echo "MATCH    ";
    $dateID = $recordDate['dateID'];
} else {
    mysql_query("insert into date(date) values('$appDate')")or die("Database write error1");
    $resultDate = mysql_query($queryDate) or die("Sorry Website Under Maintainence");
    $recordDate = mysql_fetch_array($resultDate);
    echo "NO MATCH ";
    $dateID = $recordDate['dateID'];
}

This is always triggering the else, i tried === instead of ==, i tried strcmp

Why not do the date->string massaging in the SQL query with DATE_FORMAT? — Mr. Llama, Dec 13 '12 at 22:32
the sql query is working fine, it gives result set. its just when i try to use the result set for comparison is a problem. so comparison after result is in php. — Himanshu Narula, Dec 13 '12 at 22:50
What are the values of `$appDate` and `date("Y-m-d",strtotime($recordDate['date']))`? — Jim, Dec 14 '12 at 00:19
**Heads up!** Future versions of PHP are *deprecating and removing* the `mysql_` family of functions. Now would be a great time to [switch to PDO](http://php.net/book.pdo) or [mysqli](http://php.net/book.mysqli). — Charles, Dec 14 '12 at 05:10

score 0 · Answer 1 · edited May 23 '17 at 12:28

As i assume you're comparing datetime field, you have two possibilities:

Cast field to date:

$queryDate = "SELECT * FROM your_table WHERE date(your_date_field) = date('$appDate')";

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

or

Modify your date format to be ISO compatible:

$appDate = date("Y-m-d H:i:s", $constantTime); //it defines date in format 2015-03-14 15:00:00
$queryDate = "SELECT * FROM your_table WHERE your_date_field='$appDate'";

Failing to compare php date with mysql date

1 Answers1