Search By Date

Hi Kenny,

I think you may have a typo in your month fieldname. Try renaming it to <select name="createdDate_month">

I think that will do it. You can see what SQL is being generated by adding:
'debugSql' => true,

If you want to do something more advanced, like date range searches between multiple dates, you need to combine the 3 data fields into a date in PHP and then pass it to MySQL with a WHERE.

I sometimes use this function to create date fields:

//
function showDatePulldown($fieldname) {

// show fields
print "<select name='{$fieldname}:day' style='width: auto'>\n";
print "<option value=''>day</option>\n";
foreach (range(1,31) as $dayNum) {
$selectedAttr = selectedIf(@$_REQUEST["{$fieldname}:day"], $dayNum, true);
echo "<option value='$dayNum' $selectedAttr>$dayNum</option>\n";
}
print "</select>\n";

print "<select name='{$fieldname}:mon' style='width: auto'>\n";
print "<option value=''>mon</option>\n";
foreach (range(1,12) as $monthNum) {
$monthName = date('M', mktime(0,0,0,$monthNum));
$selectedAttr = selectedIf(@$_REQUEST["{$fieldname}:mon"], $monthNum, true);
echo "<option value='$monthNum' $selectedAttr>$monthName</option>\n";
}
print "</select>\n";

print "<select name='{$fieldname}:year' style='width: auto'>\n";
print "<option value=''>year</option>\n";
foreach (range(date('Y')-1, date('Y')+5) as $year) {
$selectedAttr = selectedIf(@$_REQUEST["{$fieldname}:year"], $year, true);
echo "<option value='$year' $selectedAttr>$year</option>\n";
}
print "</select>\n";
?>

Then I can display date fields like this:
showDatePulldown('startDate');
showDatePulldown('endDate');

And combine them like this:

$startDate = mysql_real_escape_string( sprintf('%04d-%02d-%02d 00:00:00', $_REQUEST['startDate:year'], $_REQUEST['startDate:mon'], $_REQUEST['startDate:day']) );
$endDate = mysql_real_escape_string( sprintf('%04d-%02d-%02d 23:59:59', $_REQUEST['endDate:year'], $_REQUEST['endDate:mon'], $_REQUEST['endDate:day']) );

$where = "('$startDate' <= createdDate AND createdDate <= '$endDate')";

And then:
'where' => $where,

The trick when doing date queries with mySQL directly is to make sure the date looks like this: YYYY-MM-DD HH:MM:SS

So those are some other ways to do it as well. Hopefully one of those solutions will work for you. Let me know how it goes.

Well that was easy - I shouldn't be coding so late at night! But when else would we do it?

I like the date range search - I'll give that a try. Thanks Dave!