CakePHP 查找两个日期之间查询的条件 [英] CakePHP find condition for a query between two dates
问题描述
我的数据库中有一个开始日期和一个结束日期,还有一个来自表单字段的 $date 变量.我现在试图查询 $date 是数据库中的开始/结束日期或这两者之间的任何日期的所有行.
I have a start and an end date in my database and a $date variable from a form field. I am now trying to query all the rows where $date is either = start/end date in the db, or ANY date between those two.
这与 daysAsSql 如何工作的文档中描述的相反.我不知道如何让它工作.以下行不能作为控制器中的查找条件:
It's kind of the opposite of what is described in the docs of how daysAsSql works. I can't figure out how to get it to work. The following line does not work as a find condition in the controller:
'? BETWEEN ? AND ?' => array($date, 'Item.date_start', 'Item.date_end'),
非常感谢任何帮助.这让我发疯.
Any help is greatly appreciated. This is driving me crazy.
这是完整的查询和相应的 SQL:
Here is the complete Query and corresponding SQL:
$conditions = array(
'conditions' => array(
'and' => array(
'? BETWEEN ? AND ?' => array($date, 'Item.date_start', 'Item.date_end'),
'Item.title LIKE' => "%$title%",
'Item.status_id =' => '1'
)));
$this->set('items', $this->Item->find('all', $conditions));
WHERE (('2012-10-06' BETWEEN 'Item.date_start' AND 'Item.date_end') AND (`Item`.`title` LIKE '%%') AND (`Item`.`status_id` = 1))
推荐答案
$conditions = array(
'conditions' => array(
'and' => array(
array('Item.date_start <= ' => $date,
'Item.date_end >= ' => $date
),
'Item.title LIKE' => "%$title%",
'Item.status_id =' => '1'
)));
试试上面的代码,看看它是否适合你.
Try the above code and ask if it not worked for you.
根据@Aryan 的要求,如果我们必须查找在 1 个月内注册的用户:
As per @Aryan request, if we have to find users registered between 1 month:
$start_date = '2013-05-26'; //should be in YYYY-MM-DD format
$this->User->find('all', array('conditions' => array('User.reg_date BETWEEN '.$start_date.' AND DATE_ADD('.$start_date.', INTERVAL 30 DAY)')));
这篇关于CakePHP 查找两个日期之间查询的条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!