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
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.