I am currently making a hotel reservation system and wondering how can I query price column in my tbl_prices table.
Here's how my table looks like:
id date_from date_to price is_default
1 00-00-0000 00-00-0000 $95 1
2 05-25-2012 05-29-2012 $100 0
3 06-20-2012 06-24-2012 $90 0
The first row is a default price. So if somebody reserve a date that is not in row 2 and 3 the price is $95. But if the reservation date fall on 2nd and 3rd row then the price should be $100 or $90.
Example if guest want to check in from 5-24-2012 to 5-26-2012 then the price should be
1st day = $95 2nd day = $100
5-26-2012 will not be charge because it is a checkout date.
A very straight forward solution can be this:
$from = 5-24-2012 and $till = 5-26-2012
select price, date_to from my_table where date_from <= $from
ORDER BY date_from ASC LIMIT 1
Then probably you can make a check between date_to (received by this query) and $till.
if($till <= $date_to) {
// total cost is price * no. of days of stay
}
else {
// repeat above query, this time with date_to parameter in where clause
// total cost is price1 * no. of days in 1st range + .....
}
If you query once for every day, there is only one default and there are no overlapping periods for prices:
SELECT price FROM tbl_prices
WHERE ($input_date >= date_from AND $input_date <= date_to)
OR is_default = 1
ORDER BY is_default
LIMIT 1