Advanced MYSQL date query

Question

I have a 'rates' table

id | roomTypeId | periodFrom  | periodTo   | price  
1  | 49         | 2010-8-1    | 2010-8-13  | 120  
2  | 49         | 2010-8-14   | 2010-8-15  | 170  
3  | 49         | 2010-8-16   | 2010-8-31  | 120  
...  
...

DATA:

• checkInDate = 2010-8-10
• checkOutDate = 2010-8-20

My query

$query = null;  
$query .=" roomTypeId=".$obj->roomTypeId;  
$query .=" AND periodFrom>='".$obj->checkInDate."'";  
$query .=" AND periodTo<='".$obj->checkOutDate."'";     
$stmt = $this->db->query ( 'SELECT * FROM rates WHERE '.$query);   
...
...

and returns only

2  | 49         | 2010-8-14   | 2010-8-15 | 170

I want all the rates entries for that period.

thanks for any hint or suggestion

Answer 1

You only have a single row that meets both criteria ( PeriodFrom >= 2010-8-10 AND PeriodTo <= 2010-08-20). That row is row 2, which is the one returned. So obviously, your query is wrong.

1 | 49 | 2010-8-1 | 2010-8-13 | 120

Row 1 doesn't match, because even though PeriodTo is less than or equal to 2010-8-20, PeriodFrom is less than 2010-8-10. PeriodFrom disqualifies it from your conditions.

3 | 49 | 2010-8-16 | 2010-8-31 | 120

Row 3 doesn't match, because although PeriodFrom is greater than or equal to 2010-8-10, PeriodTo is not less than or equal to 2010-8-20. PeriodTo disqualifies it from your conditions.

Since you haven't really made clear what you're looking for, I'm not sure what the correct answer would be. You've asked for rows where the PeriodFrom >= SomeDate and PeriodTo <= AnotherDate, but appear to want something different.

It seems that you're looking for rooms that have either a PeriodFrom greater than some date, or a PeriodTo less than another date. If that's the case, change your AND to an OR:

SELECT * FROM RATES WHERE PeriodFrom >= '2010-8-10' OR PeriodFrom <= '2010-8-20'.

I'm suspecting that's not really what you want, either.

What exactly are you wanting your query to return? Try stating the problem in simple terms (no SQL). "I'm looking for rooms that are [some criteria]". Describe what you'd replace [some criteria] with, and perhaps someone can help.

Answer 2

It looks like you're looking for any record where the periodFrom and periodTo falls within the checkInDate and checkOutDate, but you're going about it incorrectly.

select * from RATES
where periodFrom between date(checkInDate) and date(checkOutDate) 
and periodTo between date(checkInDate) and date(checkOutDate);

The idea is that you want any record that encompasses the periodFrom -> periodTo range.

You can write this without the 'between' syntax too (just do >= and <= for each between), but this seems clearer and guarantees a proper date comparison.

Answer 3

I suspect what you might need to do is to obtain all of the rates information for periods that fall within the your defined date range, fetch the resultant data and then process this data within your choosen scripting language to work out which days need to be charged at which rate, etc.

i.e.: The query you're after is along the lines of:

SELECT * FROM rates WHERE
   (checkInDate >= periodFrom AND checkInDate <= periodTo)
OR
   (checkOutDate >= periodFrom AND checkOutDate <= periodTo)