PHP Date Range, getting idividual Dates

finding each individual date in a range

[webmongrel]

Hi,

I'm having a small problem with a hotel booking script i'm writing, here gos

The hotels have a number of different seasons in each year each with a different price per night, and i need to calculate the total cost using the date range submitted by the search, eg. 10/08/04 - 20/08/04 The dates can quite possibly span two (or more) seasons.

The only way i can see doing this is by splitting the range up into individual dates, then checking against the database for that individual date, getting the prices, then totaling up the cost.

Any help/pointers would be appreciated

Many Thanks

Matt

[Netizen]

I think a good starting point would be for you to explain how the seasons are defined in your database.

If you have something like

Hotel ¦ season ¦ seasonStart ¦ seasonEnd ¦ cost 
Hotel 1 ¦ spring ¦ 01/08/04 ¦ 01/09/04 ¦ 23.99  

to define a price for a season then you could do some clever SQL.

SELECT sum( 
 IF ( 
 (seasonStart BETWEEN '$startDate' AND '$endDate')  
 AND (seasonEnd BETWEEN '$startDate' AND '$endDate'), 
 cost*(TO_DAYS('$endDate')-TO_DAYS('$startDate')), 
 IF ( 
 (seasonStart BETWEEN '$startDate' AND '$endDate') 
 AND (seasonEnd < '$endDate'), 
 cost*(TO_DAYS(seasonEnd)-TO_DAYS('$startDate')), 
 IF ((seasonStart > '$startDate') AND (seasonEnd BETWEEN '$startDate' AND '$endDate'), 
 cost*(TO_DAYS('$endDate')-TO_DAYS(seasonStart)),0)))) 
FROM HotelSeasons 
WHERE Hotel='$hotel'

which of course I have no idea if that works, but it might be a start. Essentially it says

IF (start and end date are in this season then multiply the cost by the total number of days)
ELSE IF (start date is in the season but the end date is after the end of the season multiply the cost by the number of days from the start date to the end of that season)
ELSE IF (start date is before the start of the season and the end date is in the season multiply the cost by the number of days from the start of the season to the end date)

[I think]

[webmongrel]

Thanks alot for that,

I'll give it a go, i do have the price in a 'rooms' table at the moment and seasons a 'seasons' table (the same as you posted above, whitout the price field).

So i guess some sort of join would be suitable?

[Netizen]

Yes, a join should work - it will just make the SQL that bit more interesting :-)

[webmongrel]

lol, i bet, thanks again