CakePHP – Search for records between two dates inclusively

Often we search for records in a database table for fetching records that were created or modified on a particular date or between two dates. We match against a date field and add multiple conditions to find records with a date field value ranging between two dates.

It would look something like this:Select * from tables where CREATE_TIME >= "2007-03-14 00:00:00" and CREATE_TIME <= "2007-03-16 23:59:59"

CakePHP has built-in functions to format a query like this if you have two dates, the FROM DATE and the TO DATE.

In this example, we have two variables to store FROM and TO date limits. The function we used here is “daysAsSql’ and its a method available with the Time Helper Class. Basically these helper functions are designed to use in Views. When we need to use Helper functions in a Controller, we import the Helper using the App:import function.

After importing the Time Helper into your controller, create an instance of the Time Helper, $time = new TimeHelper();

Now call this function by passing the date ranges and the field name as parameters. $time->daysAsSql(($from)?$from:$to, ($to)?$to:$from, “date_field”) I have additional calculations here: ($from)?$from:$to & ($to)?$to:$from. This makes sure that, if one of the date input values are null or not defined, it searches all the records with the date_field value on a particular day. Explained below.

I am not doing anything with date() here. Obviously I can use date() instead of the hard-coded dates. This post just explains about the daysAsSql function available in CakePHP which allows you to fetch records between two dates without writing the SQL statement for it. So programmers who want to stick with the Cake DB Wrapper, this is a cool feature.