如何使用本周，本月，今年的CakePHP搜索数据

Question

Hi i want to search data from MySql table with This Week, This month, This Year.. Below is my Controller Code. I am not getting any data with Current DATE_SUB(NOW(), INTERVAL 1 WEEK

Controller initial Code:

 $condition = array('Order.eventid' => $id, 'Order.userid !=' => '');
            if (isset($_GET['dr']) && !empty($_GET['dr'])) {

                if ($_GET['dr'] == 2) { // This week 
                    $condition['Order.addeddate'] = 'DATE_SUB(NOW(), INTERVAL 1 WEEK)';
                }
                if ($_GET['dr'] == 3) { // This month 
                    $condition['Order.addeddate'] = 'DATE_SUB(NOW(), INTERVAL 1 MONTH)';
                }
                if ($_GET['dr'] == 4) { // This year 
                    $condition['Order.addeddate'] = 'DATE_SUB(NOW(), INTERVAL 1 YEAR)';
                }
                if ($_GET['dr'] == 5) { //  Custom date range 
                    //$condition[] = array('Order.addeddate' => 'DATE_SUB(NOW(), INTERVAL 1 MONTH)');
                }
            }
     if (isset($_GET['ot']) && !empty($_GET['ot'])) {
                if ($_GET['ot'] == 'attending') { //attending 
                    $condition['Order.orderstatus'] = '1';
                }
                if ($_GET['ot'] == 'processing') { //online_sales 
                    $condition['Order.orderstatus'] = '2';
                }
                if ($_GET['ot'] == 'manual') { //manual 
                    $condition['Order.orderstatus'] = '1';
                }
                if ($_GET['ot'] == 'refunded') { //refunded 
                    $condition['Order.orderstatus'] = '1';
                }
            }
    $this->paginate = array(
                'conditions' => $condition
            );

Answer 1

You can do it easily by use StartDate and EndDate. Add the condition like this

'conditions' => array('date(YourModel.addeddate) BETWEEN ? AND ?' => array($sdate,$edate)

Now the question how you will get this month this week this year. You can use this below code.

You can use cakephp TimeHelper

By using cakephp TimeHelper

For get this week

$sdate = $this->Time->format('y-m-d','last saturday');
$edate = $this->Time->format('y-m-d',time()); 

For get this month

$sdate = $this->Time->format('y-m-01',time());
$edate = $this->Time->format('y-m-d',time());  

For get this year

$sdate = $this->Time->format('y-01-01',time());
$edate = $this->Time->format('y-m-d',time()); 

For details TimeHelper Doc

Without TimeHelper

For get this week

$sdate = date('y-m-d', strtotime("last saturday"));
$edate = date("y-m-d"); 

For get this month

$sdate = date('y-m-01');
$edate = date("y-m-d"); 

For get this year

$sdate = date('y-01-01');
$edate = date("y-m-d"); 

Now if you want to get last 7 days or last 30 days, you can use strtotime.

For get last 7 days

$sdate = date('Y-m-d', strtotime("-1 weeks"));
$edate = date("y-m-d"); 

For get last 30 days

$sdate = date('Y-m-d', strtotime("-30 days"));
$edate = date("y-m-d");

For get last 1 year

$sdate = date('Y-m-d', strtotime("-1 year"));
$edate = date("y-m-d");

I think now you can easily solve your problem.