CODEWITHSUNDEEP
phpcakephpcakephp-2.0cakephp-2.1cakephp-2.3
abcy122
abcy122

Reputation: 125

Retrieve data based on year and month in cakephp

I am trying to create a get statements function and I have some problems defining the date range.

The data will be passed via get method in the following format: ?year=yyyy&month=mm The relevant column of the table has datetime type (2012-02-01).

I checked the TimeHelper class and more particularly the TimeHelper::daysAsSql($begin, $end, $fieldName, $timezone = NULL) but i am not sure if it applies for this case.

I was thinking if i can create a date in the format of Y-m using the two variables year and month and then use it in the conditions for retrieving the data but I am sure there is a more efficient and proper way. Could anyone help?

$user_id = $Client['Client']['id'];
    $year = $this->request['url']['year'];
    $month = $this->request['url']['month'];
    //$date_created = date('Y-m');


  if (!empty($user_id) && (!empty($date_created))) {


        $Reports = $this->Report->find('all', array(
        'fields' => array('amount','currency','date_created','invoice_no'),
        'conditions' => array(
            'Report.client_id'=> $user_id,
            'Report.date_created >=' => $date_created, 
            'Report.date_created <=' => $date_created

             ),
        )
    );

Upvotes: 1

Views: 3774

Answers (1)

Supravat Mondal
Supravat Mondal

Reputation: 2594

MONTH(dateColumn) Returns an integer that represents the month of the specified date.

YEAR(dateColumn) Returns an integer that represents the year of the specified date.

InCakephp use as 

$user_id = $Client['Client']['id'];
    $year = $this->request['url']['year'];
    $month = $this->request['url']['month'];
    //$date_created = date('Y-m');


    if (!empty($user_id) && (!empty($date_created))) {


        $Reports = $this->Report->find('all', array(
                'fields' => array('amount','currency','date_created','invoice_no'),
                'conditions' => array(
                        'Report.client_id '=> $user_id,
                        'MONTH(date_created ) >='=> $month,
                        'YEAR(date_created ) <=' => $year

                ),
        )
        );

Upvotes: 1

Related Questions