CakePHP Search Between Two Date Entries

Question

I am creating a small web application that allows users to reserve office space and equipment. For reservations, they enter a start and end date.

When a user tries to find out if any (for example) car is available in 2012-10-23, and the database contains records about the reservation date Start: 2012-10-20 and End: 2012-10-25 for (say) everything cars, how to include all dates between my dates in the search?

The variable $date gets this value from the date search field.

This, unfortunately, does not work, and I cannot 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)); 

Answer 1

There is a simpler solution, but thanks for the help:

(as a condition in find :)

 array('Equipment.date_start <= ' => $date, 'Equipment.date_end >= ' => $date ), 

Answer 2

 $start = date('Ym-d'); $end = date('Ym-d', strtotime('+1 month')); $conditions = array('Event.start <=' => $end, 'Event.end >=' => $start); $this->Event->find('all', array('conditions' => $conditions)); 

Answer 3

If you are on cakephp 2.0, this is the answer http://book.cakephp.org/2.0/en/core-libraries/helpers/time.html#TimeHelper::daysAsSql

If you are using cakephp 1.3 you can use TimeHelper, just import it and use the same function as the example in the documentation, here http://book.cakephp.org/1.3/en/view/1471/Formatting

Answer 4

If you have one date and 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 so 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)); 

Answer 5

You cannot use database columns with this BETWEEN -syntax. If you assign strings in an array, CakePHP will quote them. The same goes for numerical values ​​depending on the database setup.

CakePHP will indicate numeric values ​​based on the type of field in your database.
- see http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#complex-find-conditions

If you still want to use BETWEEN , you can write your requests to an array, because CakePHP will not exit these keys, but only the values.

CakePHP only eludes array values. You should never put user data in keys. This will make you vulnerable to SQL injection.
- see http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#complex-find-conditions

Regarding 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 and time functions, do not quote them. If you are using a specific date, put it in quotation marks.

Answer 6

Here is an example CakePHP BETWEEN request.

I define my arrays as variables, and then using these variables in my CakePHP search 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)); 

Link from