CODEWITHSUNDEEP
phpmysqldatetime
Kavvson Empcraft
Kavvson Empcraft

Reputation: 443

Check if time range doesn't collide with another datetime range

My user inputs two values in datetime [html form] fields :: book_from f.e 2015-09-15 16:44:39 book_to 2015-09-17 16:44:39 then the query should check if the provided dates doesn't collide with a existing reservation.

My query looks as following - but I feel like there is no way for it to work.

A raw php code -> "SELECT * FROM `tbl_rezerwacje` WHERE '".$_POST['book_from']."' between `date_from` and `date_to`  AND `nr_sali` = '".$place."'";

SQL dump -> SELECT * FROM `tbl_rezerwacje` WHERE '2015-09-15 16:44:39' between `date_from` and `date_to` AND `nr_sali` = 'F115'

Upvotes: 0

Views: 2254

Answers (2)

Szenis
Szenis

Reputation: 4170

WHERE expects to have a colum name after it and not a value.
Also between does not work that way.

EDIT: Strawberry's explanation

Consider the following

  1 2 3 4 5 6 7 8 9 

A |---|

B    |-----|

C            |----|

D   |-----------|

The test for overlaps is simpler than Szenis seems to appreciate.

Event B overlaps event A because event B starts before event A ends, and ends after event A starts.

Event C does NOT overlap event B because although event C ends after event B starts, it starts after event B ends

Event D overlaps all the other events because event D starts before all of the other events end, and ends after all the other events start.

So the test for overlaps is simply:

y_start < x_end AND y_end > x_start

The answer
Thanks to Strawberry the answer is much shorter then before

SELECT * 
FROM `your_table`
WHERE (`date_from` < end_date AND
       `date_to` > start_date) AND 
       `nr_sali` = your_book_id

If there is a record found the book is not available during the given dates.

Upvotes: 4

Tomas Ceruti
Tomas Ceruti

Reputation: 136

$from_date -> user input of start date
$to_date -> user input of end date
'start' -> DB colum for start date
'end' -> DB column for end date

ANSWER

SELECT * FROM table_reservations WHERE start < "$to_date" AND end > "$from_date"

If a record matches this query then it overlaps another booking (you have to validate that the daterange from the user is valid -> $from_date < $to_date)

Upvotes: 0

Related Questions