How to convert sql query to cakephp query and use in controller

Question

SELECT count(*) FROM payments WHERE status='Success' and createddate BETWEEN '2020-05-05' AND '2020-05-06'

$numcount= TableRegistry::get('Payments')->find('all', array(
    'conditions' => array('status'=>'Success','and' => array(
    array('createddate <= ' => $date1,
        'createddate >= ' => $date2
    )
))))->count();

Please help. Thanks in advance. I tried but the code doesn't work.

What does "doesn't work" mean? Is your createddate field just a date, or does it have the time in it too? — Greg Schmidt
– Greg Schmidt, Commented May 6, 2020 at 17:58
if i execute this query showing zero always. and createddate have date only — prabhu
– prabhu, Commented May 7, 2020 at 0:33
yes $date1='2020-05-05' and $date2='2020-05-06' eg. SELECT count(*) FROM payments WHERE status='Success' and createddate BETWEEN $date1 AND $date2 — prabhu
– prabhu, Commented May 7, 2020 at 0:45
So, "between 2020-05-05 and 2020-05-06" means "greater than or equal to 2020-05-05 and less than or equal to 2020-05-06". You've got them the other way around. There's no date less than or equal to the 5th and also greater than or equal to the 6th. — Greg Schmidt
– Greg Schmidt, Commented May 7, 2020 at 0:50

Greg Schmidt · Accepted Answer · 2020-05-07 00:52:17Z

1

You've got your dates backwards. Also, you don't need an extra 'AND' in your conditions, they're all AND by default.

$numcount= TableRegistry::get('Payments')->find('all', array(
    'conditions' => array(
        'status' => 'Success',
        'createddate >=' => $date1,
        'createddate <=' => $date2
    )
))->count();

answered May 7, 2020 at 0:52

Greg Schmidt

5,1482 gold badges17 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert sql query to cakephp query and use in controller

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related