CODEWITHSUNDEEP
phpmysqlcakephpcakephp-2.0
Naresh
Naresh

Reputation: 2781

How to search data with This Week, This month, This Year in CakePHP

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
            );

enter image description here

Upvotes: 0

Views: 1922

Answers (2)

Karan
Karan

Reputation: 2112

you can use conditions like below

//current month
$conditions = array('MONTH(Model.field) = MONTH(CURDATE())', 'YEAR(Model.field) = YEAR(CURDATE())');

//current week
$conditions = array('YEARWEEK(Model.field,1) = YEARWEEK(CURDATE(), 1)');
//current year
$conditions = array('YEAR(Model.field) = YEAR(CURDATE())');

Upvotes: 0

Alimon Karim
Alimon Karim

Reputation: 4469

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.

Upvotes: 6

Related Questions