Home » CakePHP, PHP/MySQL, Programming Techniques » CakePHP – Search for records between two dates inclusively

3

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.

See the Example below:

$from = '14-Mar-2007';
$to = '16-03-2007';
App::import('Helper', 'Time');
$time = new TimeHelper();
echo '('.$time->daysAsSql(($from)?$from:$to, ($to)?$to:$from, "created").')';

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.

The above example outputs this:

((created >= ’2007-03-14 00:00:00′) AND (created <= ’2007-03-16 23:59:59′))

Now you can use this as a Condition in your Find functions or as a Filter in your Pagination queries.

Examples:

$filters = array();
$filters = '('.$time->daysAsSql(($from)?$from:$to, ($to)?$to:$from, "created").')';
$records = $this->paginate('ModelName', $filters);

or

$conditions = array();
$conditions = '('.$time->daysAsSql(($from)?$from:$to, ($to)?$to:$from,  "created").')';
$this->data = $this->ModelName->find('all', array('conditions' =&gt; $conditions));

If $from was null the query generated would look like this:
((created >= ’2007-03-16 00:00:00′) AND (created <= ’2007-03-16 23:59:59′))

Please let me know if you have any questions.

3 Comments

  1. mark says:

    isnt this total overhead?
    you can simply use date() or date(FORMAT, time()) or even date(FORMAT, strotime(OTHERFORMAT))

  2. Aneeska says:

    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.

  3. this article really helps especially the paginate one. Keep it up men.
    God Bless!

Leave a Reply

Page optimized by WP Minify WordPress Plugin