CakePHP find and the SELECT BETWEEN query syntax

In an effort to get more CakePHP find query examples out into the world, here are two examples of how to perform SQL "SELECT BETWEEN" queries using the CakePHP find method.

In my first find query example, I'm trying to find all the records from the logfile_records database table where the date column is between the two dates specified:

return $this->LogfileRecord->find('all',
  'conditions' => array('LogfileRecord.date between ? and ?' => array($start_date, $end_date)));

Quick discussion: If you're familiar with CakePHP, you'll know that by convention, the LogfileRecord model name implies a database table named logfile_records. After that, the thing I really like about the "between" query syntax is that we can use the ? character as a placeholder for our variables. This makes the queries much more readable than they would be otherwise.

A second CakePHP SELECT BETWEEN query example

In this second CakePHP BETWEEN query example, I'm doing almost the same thing, but I'm only returning two fields from my query, specifically the uri and page_views fields. Also, because I think all of the array syntax gets messy, I'm defining my arrays as variables, and then using those variables in my CakePHP find function call:

// just return these two fields
$fields = array('uri', 'page_views');

// use this "between" range
$conditions = array('LogfileRecord.date BETWEEN ? and ?' => array($start_date, $end_date));

// run the "select between" query
$results = $this->LogfileRecord->find('all', 
             array('fields'=>$fields, 
                   'conditions'=>$conditions));

CakePHP BETWEEN query with a numeric range

As a final note, here's a short BETWEEN example from the CakePHP Cookbook:

array('Post.id BETWEEN ? AND ?' => array(1,10))

That example is nice because it shows that you can use the BETWEEN syntax for other things besides date range queries.

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.