BETWEEN not working in MySQL

Question

I am implementing a hotel booking site where hotel owner can book rooms according to availability . For checking the number of rooms available , I have written the code like :

$q=" SELECT * 
        FROM  tbl_roombook
        WHERE
        hotel_id='$h_id'AND
        start_date
        BETWEEN  '$date1'
        AND  '$date2'
        AND end_date
        BETWEEN  '$date1'
        AND  '$date2'

    ";
$res = $this->db->returnArrayOfObject($q);
//var_dump($res);

In my database 2 rooms are booked from 2011-12-25 to 2011-12-31 . And my echo $date1 is 2011-12-29 and $echo date2 is 2011-12-30 . But $q returns empty results ! Why ?

Do you get any SQL errors? Does the query work in MySQL client or in phpMyAdmin? — miq, Dec 10 '11 at 08:39
Try `start_date <= '$date2' AND end_date >= '$date1'` [See this answer](http://stackoverflow.com/a/143568/73226) for explanation. — Martin Smith, Dec 10 '11 at 09:05
Is there a `start_date` and `end_date` entry between `2011-12-29` and `2011-12-30`? — Shirish11, Dec 10 '11 at 09:19
When debuging queries it's very useful to have a look at the mysql query log to actually see what gets executed. Your problem here is the MySQL query, and convoluting the question with PHP can only make the problem seem more complex. — cherouvim, Dec 10 '11 at 09:23

score 0 · Answer 1 · answered Dec 10 '11 at 09:16

For the example you gave it should return no results... start_date=2011-12-25 and is not between 2011-12-29 and 2011-12-30 so the expression is false right there. I think what you want is to see if there is any overlap between the booked range and the new possible booking range right?

To do this you will want to instead check

start_date > '$date2' OR end_date < '$date1;

assuming that $date1 is always before $date2.

score 0 · Answer 2 · answered Dec 10 '11 at 09:36

The example will return no results for the date1 and date2 entries that u have provided. That is because assuming your start_date = 2011-12-25 and end_date = 2011-12-31.
None of both the values is within your provided date range 2011-12-29 and 2011-12-30.
Hence no result is generated. You could use it in reverse manner

'$date1' BETWEEN start_date AND end_date 
 OR  '$date2' BETWEEN start_date AND end_date

BETWEEN not working in MySQL

2 Answers2