CakePHP Search between 2 Date Records
Solution 1
There is a simpler solution to this, but thanks for the help:
(As a condition in the find:)
array('Equipment.date_start <= ' => $date,
'Equipment.date_end >= ' => $date
),
Solution 2
Here is CakePHP BETWEEN
query example.
I'm defining my arrays as variables, and then using those variables in my CakePHP find function call:
// just return these two fields
$fields = array('uri', 'page_views');
// use this "between" range
$conditions = array('Event.date BETWEEN ? and ?' => array($start_date, $end_date));
// run the "select between" query
$results = $this->Event->find('all',
array('fields'=>$fields,
'conditions'=>$conditions));
Solution 3
In case if you have one date and you want to use BETWEEN
$date_start = start date;
$date_end = date_end;
$conditions = array(
'conditions' => array(
'date(Equipment.create) BETWEEN ? AND ?' => array($date_start, $date_end),
)));
$this->set('equipments', $this->Equipment->find('all', $conditions));
This is the case if you have From AND To
in your case
$date_start = start date;
$date_end = date_end;
$conditions = array(
'conditions' => array(
'Equipment.start_date >=' => array($date_start),
'Equipment.end_date <=' => array($date_end)
));
$this->set('equipments', $this->Equipment->find('all', $conditions));
Solution 4
$start = date('Y-m-d');
$end = date('Y-m-d', strtotime('+1 month'));
$conditions = array('Event.start <=' => $end, 'Event.end >=' => $start);
$this->Event->find('all', array('conditions' => $conditions));
Solution 5
You cannot use database columns with this BETWEEN
-syntax. If you assign strings in the array, CakePHP will quote them. Same for numeric values depending on your database setup.
CakePHP will quote the numeric values depending on the field type in your DB.
– see http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#complex-find-conditions
If you still want to use the BETWEEN
, you can write your queries into the array key because CakePHP will not escape the keys, but only the values.
CakePHP only escapes the array values. You should never put user data into the keys. Doing so will make you vulnerable to SQL injections.
– see http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#complex-find-conditions
Concerning your problem:
$this->Model->find('all', array(
'conditions' => array(
'"YYYY-MM-DD" BETWEEN Model.date_start AND Model.date_end',
),
));
You can even work with MySQL date and time functions:
$this->Model->find('all', array(
'conditions' => array(
'CURDATE() BETWEEN Model.date_start AND Model.date_end',
),
));
If you use date/time functions, do not quote them. If you use a specific date put it in quotes.
Joshua
Updated on June 09, 2020Comments
-
Joshua almost 4 years
I am building a small Web App that lets users reserve Office Rooms and Equipment. For the Reservation they enter a Start and an End Date.
When a user tries to find out if any (for example) car is available on 2012-10-23, and the database holds reservation date records of Start:
2012-10-20
and End:2012-10-25
for (lets say) all the cars, how do I include all the dates between my date entries in the search?The
$date
variable gets it's value from the Date Search Form Field.This, unfortunately does not work, and I can't figure out how to use daysAsSql for this query:
$conditions = array( 'conditions' => array( '? BETWEEN ? AND ?' => array($date,'Equipment.date_start','Equipment.date_end'), ))); $this->set('equipments', $this->Equipment->find('all', $conditions));
-
Arun Jain almost 12 yearsYou should check that the date should not be within
startdate
andenddate
. nuts-and-bolts-of-cakephp.com/2008/06/30/… -
Joshua almost 12 yearsBased on the Link you provided, I changed the code to this: ` '? BETWEEN ? AND ?' => array($date,'Equipment.date_start','Equipment.date_end'), ` Unfortunately that doesn't do anything …
-
-
nappo almost 12 yearsi'm just curious - can't find anything about
dateRange
or start / end in docs, api or code, can you please provide a link? -
Anooj P almost 12 yearsnuts-and-bolts-of-cakephp.com/2008/06/30/… Refer this also
-
Joshua almost 12 yearsThanks, I am aware of the docs, but I can't get daysAsSql to work with this. (I am on CakePHP 2)