mysql - 使用 mysql 或 cakephp 以月为单位搜索结果

Question

大家好我有一个问题搜索结果月份使用 MySql 或 Cakephp 我的表名称是 state_supplies 和数据是

MySql 查询是:

SELECT * FROM `state_supplies` WHERE created=DATE_FORMAT(NOW(), '%m')

Cakephp搜索代码是

$this->StateSupply->find('all', array(
                      'conditions'=> array(
                        'unitid'=>$this->Session->read('Auth.User.unitid'),
                        'created'=>'MONTH(CURDATE())'
                      )                 
                    )
);

Answer 1

试试这个:

在 Mysql 中:

$first_day_this_month = date('Y-m-01 H:i:s'); // hard-coded '01' for first day
$last_day_this_month  = date('Y-m-t H:i:s');
$sql="SELECT * FROM state_supplies WHERE created between '$first_day_this_month' and '$last_day_this_month' "

Cakephp代码

Details

更新正确答案的代码

 $this->StateSupply->find('all',array(
                'conditions'=>array(
                    'unitid'=>$this->Session->read('Auth.User.unitid'),
                    'crop'=>$this->request->data['StateSupply']['crop'],
                    'state'=>$this->request->data['StateSupply']['state'],
                    'created BETWEEN ? AND ?'=>array(date('Y-m-01',strtotime('this month')),date('Y-m-t',strtotime('this month')))
                        )                   
                    )
                );