CODEWITHSUNDEEP
mysqlcakephpvalidationbetween
user1428033
user1428033

Reputation: 221

CakePHP: Search BETWEEN datetimes and validate form

I'm stuck with this. My own skills aren't enough to solve this myself.

I have a form with these fields:

What I try to achieve:

So, the query would look something like this:

SELECT *
FROM bookings
WHERE place_id=".$_POST['placeId']."
AND('".$_POST['startDate']."'
BETWEEN  start_date 
AND end_date
OR '".$_POST['endDate']."'
BETWEEN start_date
AND end_date)"

If this returns NULL, the submit will success. How to get this working with CakePHP? Please, help...

Upvotes: 1

Views: 2262

Answers (2)

Sam Delaney
Sam Delaney

Reputation: 1305

From a separation of logic perspective, I think this should be handled in your controller. Building from Ollie Jones advice, upon post-back from the user's browser, you should do something like the following: 

$this->Booking->find('all', array('conditions' => array('Booking.start_date BETWEEN' => array($new_start_date, $new_end_date), 'Booking.end_date BETWEEN' => array($new_start_date, $new_end_date))

$new_start_date should be set to $this->data['Booking']['start_date'] and the same goes for $new_end_date. The answer just starts to look messy otherwise!

If the query returns nothing then you can go ahead and save your new booking. If there are bookings, you should advise your user accordingly.

Upvotes: 1

O. Jones
O. Jones

Reputation: 108839

The comments warning you about SQL injection are correct.

Using BETWEEN for DATETIME data type search is problematical. I'm not sure this is your problem, but it might be.

Consider a DATETIME value of, say, '2013-04-13 11:00:00' You'd think this would be BETWEEN '2013-04-13' AND '2013-04-13', but it isn't, because it's after '2013-04-13 00:00:00'.

One of the unpleasant problems with this use of BETWEEN is that single day ranges don't work.

What you need for a date range match is

      date_to_test >= start_date
  AND date_to_test < end_date + INTERVAL 1 DAY

Upvotes: 1

Related Questions