Having issue comparing dates in WHERE clause of select statement

[rodb]

Good Evening,
I have a function that is use to filter data based on the due_date. The activities table has a defined date field (due_date) that is to be compared with the current date. However, I am not getting the correct results. I have been researching but can not seem to determine the issue other than the variable and field are different formats. Have not been able to determine how to correct the issue.

[php]
function find_activities_overdue($overdue){
global $con;
$tday = date(‘Y-m-d’); // current date

$sql = “SELECT *”;
$sql .= " FROM activities";
if ($overdue) {
$sql .= " WHERE (due_date < $tday)";
} else {
$sql .= " WHERE (due_date >= $tday)";
}
$sql .= " ORDER BY due_date DESC";

$result = mysqli_query($con,$sql);
if($result){
$results = while_loop($result);
} else {
check_query($result);
}
return $results;
}
[/php]

Any help would be greatly appreciated.
Rod

---

help.txt (698 Bytes)

[astonecipher]

It’s good that you are using mysqli_. The next step is to use prepared statements to prevent sql injection and accidental errors.

What data type is due_date? If it uses the default MySQL date format, then this format string gives you that:

[php]$tday = date(‘Y-m-d H:i:s’);[/php]

[Strider64]

I haven’t tried this out, so I don’t now if it will work or not:

[php]if ( $overdue ) {
$sql .= “WHERE due_date < CURDATE()”;
} else {
$sql .= “WHERE due_date >= CURDATE()”;
}[/php]

Though like astonecipher said the due_date must be in MySQL format ( 0000-00-00 00:00:00 )

[rodb]

The format of due_date is YYYY-MM-DD; i.e. 2015-09-01

Using the CURDATE() function did the trick.

Thanks for the help