CakePHP 3.6 - Find All Records Created Between Two Dates

Question

I am looking for all records between two dates

My variables

 $start = '01/01/2009';
 $end = '07/24/2019';

I have tried

$gross = $this->CartOrders->find('all')->where(['placed >=' => $start])->andWhere(['placed <=' => $end])->all();

Query Snippet for above

... FROM cart_orders CartOrders 
WHERE (placed >= :c0 AND placed <= :c1) 
[params] => Array ( 
     [:c0] => Array ( [value] => 01/01/2009 [type] => datetime [placeholder] => c0 ) 
     [:c1] => Array ( [value] => 07/24/2019 [type] => datetime [placeholder] => c1 ) )

Results in

 Cake\ORM\ResultSet Object ( [items] => Array ( ) )

I have also tried

$gross = $this->CartOrders->find('all')->where(function($exp) use($start,$end) {
        $exp->lte('placed', $end);
        $exp->gte('placed', $start);
        return $exp;
    })->all();

I also have tried

$gross = $this->CartOrders->find('all')->where(function($q) use($start,$end) {
        return $q->between('CartOrders.placed', $start, $end, 'date');
    })->all();

Any ideas on how I can accomplish this?

I just tried the andWhere version, and checked the dates, if I remove the andWhere, it gives me everything created after and equal to $start, but when I add the andWhere, it gives me nothing... — Jeffrey L. Roberts, Jul 24 '19 at 00:27
Any chance it's a locale issue, and the end date is being misintpreted? Maybe try putting those dates into `FrozenDate` objects and use those in the `where`? — Greg Schmidt, Jul 24 '19 at 03:01
`MM/DD/YYYY` is most definitely the wrong format, your database most likely uses `YYYY-MM-DD` in respective date(time)-ish columns, and if it doesn't, then you need to change that, because `02/01/2019` is larger than `01/02/2019`. — ndm, Jul 24 '19 at 08:15

score 2 · Answer 1 · answered Jul 25 '19 at 13:26

Use QueryExpression

use Cake\Database\Expression\QueryExpression;

$query = $this->CartOrders->find()
->where(function (QueryExpression $exp, Query $q) use ($start,$end){
    return $exp->between('placed', $start, $end);
});

Probably add a time at condition if the user tried to search within the same day

return $exp->between('placed', $start . " 00:00:00", $end . " 23:59:59");

score 1 · Answer 2 · answered Jul 24 '19 at 05:30

1

Try using

$this->CartOrders->find('all', array('conditions' => array(
        'date(placed) BETWEEN "'.$start.'" AND "'.$end.'"')));

It's an unorthodox solution but its something that has worked for multiple scenarios for me

answered Jul 24 '19 at 05:30

ameer nagvenkar

381
1
10

1

Please never ever insert dynamic data into flat array values, this is a possible SQL injection vulnerability! Always use the `key => value` syntax, safe expressions, or bindings! – ndm Jul 24 '19 at 06:40
In case this is better ````$this->CartOrders->find('all', 'conditions' => array('CartOrders.placed between ? and ?' => array($start_date, $end_date)));```` – ameer nagvenkar Jul 24 '19 at 06:45
That would be better, yes, but the question is about CakePHP 3.x, that style is for CakePHP 2.x, it's not supported in 3.x anymore. – ndm Jul 24 '19 at 06:54

score 1 · Answer 3 · answered Jul 24 '19 at 06:21

1

$this->set('gross',$this->CartOrders-> find(
                'all', array(
                    'conditions' => array(
                        'CartOrders.placed >=' => $start,
                        'CartOrders.placed <=' => $end
                    ))
            ));  // here gross is a variable to store the data from DB and CartOders is the Model name

answered Jul 24 '19 at 06:21

Ramya

95
1
1
7

How is this supposed to solve the problem? That snippet would produce the exact same query as the examples in the question. – ndm Jul 24 '19 at 06:51

score 0 · Accepted Answer · answered Jul 26 '19 at 06:49

This turned out to be a date format issue.

The following solved my problem.

$start = '01/01/2009';
$end = '07/24/2019';

$start = DateTime::createFromFormat('d/m/Y', $start);
$end = DateTime::createFromFormat('d/m/Y', $end);

$gross = $this->CartOrders->find('all')->where([
     'placed >=' => $start->format('Y-m-d')
])->andWhere([
     'placed <=' => $end->format('Y-m-d')
])->all();

This link helped

PHP convert date format dd/mm/yyyy => yyyy-mm-dd

CakePHP 3.6 - Find All Records Created Between Two Dates

4 Answers4