MySQL query DATE comparison

Question

I have a problem with my query. Its giving me the correct result for some cases and for some it does not, so if anyone could tell me what to change it'd be amazing cause I'm stuck with it for some time now. Here is my table data:

Table reservation_logs:

log_id      int(15) auto_increment   Primary   Unique  
room_log    int(10)    
dt_from     date        
dt_until    date

Table apartments:

apartman_id     int(10) auto_increment   Primary   Unique   
apartman_name   int(10)

My php code for date:

$date_from = 'text input date';
$date_until = 'text input date';

$myquery = ("SELECT * FROM reservation_logs WHERE room_log = '$apartman_id'  
AND(((dt_from >= '$date_from' AND dt_from <= '$date_until') 
AND (dt_until >= '$date_until' OR dt_until <= '$date_until')))");

Now I input some dates I should get correct apartman_id, if its busy or not. Here is case when this is working: when $date_from equals 1st day in the month and $date_until equals last day in a month. But for example if I choose dates $date_from equals 16th of the month and $date_until equals 17th of the month I get incorrect results if room is busy from 15th of the month until 19th lets say. I hope I didn't complicate too much I just wanted for more people to get idea behind this.

Answer 1

So you want to find bookings between $date_from and $date_until, even if they just overlap this date range? Something as simply as this should suffice...

SELECT * FROM reservation_logs WHERE room_log = '$apartman_id'  
AND dt_from <= $date_until 
AND dt_until >= $date_from ;

It simply excludes any booking which starts after your date range, and any booking which finishes before your date range. Anything that remains must be in the date range or overlapping it.

Answer 2

dt_until >= '$date_until' OR dt_until <= '$date_until'

Will always be true...

Answer 3

dt_from >= '$date_from' AND dt_from <= '$date_until'

...and this is only true if $date_from = $date_until.