Pricelist checking for a booking app

meigwil

Hi,

I'm building an app to book holiday cottages for a client.

A problem that I have is how to price a stay.

An user selects two dates. After validating that they're available etc, I then need to calculate the cost. The client has given me a pdf of his procelist, which I've turned into a db table.

He has 5 accommodation units, and each have different prices for different times of the year, and for different lengths of time (i.e. a week costs less than 7 nights)

Here's what I'm using http://www.gwilym.net/table_phpbuilder.html

and here's the SQL:

$sql = "SELECT 1_week FROM (SELECT * FROM tariffs WHERE to_date >= '2008-08-16') AS temp WHERE from_date <= '2008-08-30' ;";

Now, the above dates should cost £1800, but I only get back one record, for £900.

I think that the SQL is OK, and that I need to change the dates that I put into it, and maybe make more than one query, depending on the dates given.

Any advice? I'm pretty stuck.

Many thanks,

Mei

leatherback

You have selected the RATE for that period, which is correctly returned as 900, presumably a weekly rate. You then need to multiply the rate with the period (2 weeks) to get the total cost.

However, if you have 2 weeks, I think you would need the rate for week_2?

meigwil

You're right. I need to think of it as the rate.

(2_week is for the 2nd accommodatin unit).

I think I need to split the requested dates into week blocks, then I can check the rate for each block.

Has anyone else any thoughts on this?

Thanks for your help,

Mei

leatherback

I would create a table rates:
[rateid][accomodationnr][periodnr][rate type][value]

=
key int: identifier
int: Accomodation identifier
int: Rate type [1:daily, 2: midweek / we, 3: weekly
flt: cost per period

and a table accomodation
[acc_id][acc_description]
key int: identifier
text: description

and a table periods
[period id][startdate][enddate]
key: int: identifier
timestamp: timeperiod start
timestamp: timeperiod end

So you can
select from period where $date < startdate && $date < enddate
left join rates on periodnr = periodid
left join accomodation on acc_id = accomodationnr
where accomodation=$accomodation && rate_type='weekly'

or something

meigwil

Hey, thanks for that.

So you're splitting the data into 3 separate tables? This looks an interesting way about it.

I've got an idea of my own, but I may try this later if (or when!) my idea doesn't work.

Thanks again for your help.

Mei

leatherback

The idea behind it is that you store each element of information only once and you can reuse as much of your code as possiblefor differente situations. The code I showed allows for the same query to be used to get information for any property, and type of period without any adjustments. As such, you can build one very simple method to get the price, and avoids complex if/then else and switch structures.