CakePHP find condition for a query between two dates

user_1456294 0
Viewed 51514

I have a start and an end date in my database and a $date variable from a form field. I am now trying to query all the rows where $date is either = start/end date in the db, or ANY date between those two.

It's kind of the opposite of what is described in the docs of how daysAsSql works. I can't figure out how to get it to work. The following line does not work as a find condition in the controller:

'? BETWEEN ? AND ?' => array($date, 'Item.date_start', 'Item.date_end'),

Any help is greatly appreciated. This is driving me crazy.

Here is the complete Query and corresponding SQL:

$conditions = array(
            'conditions' => array(
            'and' => array(
                '? BETWEEN ? AND ?' => array($date, 'Item.date_start', 'Item.date_end'),
                'Item.title LIKE' => "%$title%",
                'Item.status_id =' => '1'
                )));

        $this->set('items', $this->Item->find('all', $conditions));



WHERE (('2012-10-06' BETWEEN 'Item.date_start' AND 'Item.date_end') AND (`Item`.`title` LIKE '%%') AND (`Item`.`status_id` = 1))
between cakephp time date
6 Answers
$data=$this->post->find('all')->where([ 'id'=>$id,
'price between'=>$price1,'and'=>$price2])->toArray();

This query works as following:

select * from post where id=$id and price between $price1 and $price2;

" -- 'price between'=>$price1 --" become "price between $price1"

user_81983600

Use this 

       $today = new DateTime( date('Y-m-d'));

       $fiveYearsBack = $today->sub(new DateInterval('P5Y'));
user_110671500
Related