PHP Mysql Reservation Daily Rate Script

[Danny620]

Hi All,

 

I am building a hotel reservation script so far so good, however I need to be able to put in a day rate that overrides the normal price e.g

 

a Single Room can be £60 in the week but at weekend it can be £70

 

My database is setup like

 

id

hotel_id

room_title

normal_price_per_night

 

I think i need to to setup another table where user can put his prices in for a certain day or period of time.

 

like

id

room_id

price_rate

start_date

end_date

 

Please advise which is the best way to do this and any examples for selecting the data and pricing would be a very big help.

 

 

[ginerjm]

Looking at your plan it seems very workable.  I would revise the two tables to be sure that you can match like room titles/ids since currently they seem to be different.

 

Process then would be to seek records from both tables that match the standard room type desired and any 'special' room rate records that match that type and the specified date range.  Pretty straight forward to me.

[Danny620]

Hi Ginerjm, Thank you for your reply would you be kind to give me examples

[ginerjm]

The way it works in forums - you write your code.  We help if you have a bug or problem with it.

[Barand]

If there is always a different weekend price then you might want to to hold both price in the price record. Alternatively, hold two (or more) records for every week of the year.

Room
-----------
room_id
hotel_id
room_title

Price
-----------
id
room_id
week_price
wkend_price
start_date
end_date

This allows you price file dates to define high,mid and low-season prices and also special prices such as Easter Weekend, Christmas and New Year.

 

How you match the prices to the rooms will depend on the structure of your bookings table

[Danny620]

i need to be able to select prices that fall within certain dates i.e select 2014-01-30 - 2014-02-05 if for example a day is not in the it reverts by to default price can anyone show me a mysql query

[Barand]

Assuming a database structure like this

mysql> SELECT * FROM room;
+--------+---------+------------+--------------+
| roomid | hotelid | room_title | normal_price |
+--------+---------+------------+--------------+
|      1 |       1 | Geranium   |           50 |
|      2 |       1 | Delphinium |           50 |
|      3 |       1 | Rose       |           70 |
+--------+---------+------------+--------------+

mysql> SELECT * FROM price;
+---------+--------+-------+------------+------------+
| priceid | roomid | price | start_date | end_date   |
+---------+--------+-------+------------+------------+
|       1 |      1 |    60 | 2014-02-02 | 2014-03-29 |
|       2 |      2 |    60 | 2014-02-02 | 2014-03-29 |
|       3 |      3 |    75 | 2014-02-02 | 2014-03-29 |
|       4 |      1 |    90 | 2014-03-30 | 2014-04-02 |
|       5 |      2 |    90 | 2014-03-30 | 2014-04-02 |
|       6 |      3 |   105 | 2014-03-30 | 2014-04-02 |
|       7 |      1 |    65 | 2014-04-03 | 2014-06-15 |
|       8 |      2 |    65 | 2014-04-03 | 2014-06-15 |
|       9 |      3 |    80 | 2014-04-03 | 2014-06-15 |
+---------+--------+-------+------------+------------+

mysql> SELECT * FROM roombooking;
+----+-----------+--------+-------------+
| id | bookingid | roomid | bookingdate |
+----+-----------+--------+-------------+
|  1 |       123 |      1 | 2014-02-01  |
|  2 |       123 |      1 | 2014-02-02  |
|  3 |       123 |      1 | 2014-02-03  |
+----+-----------+--------+-------------+

then the sort of query you would need is

SELECT rb.bookingid
    , r.room_title
    , rb.bookingdate
    , IFNULL(p.price, r.normal_price) as price
FROM roombooking rb
    INNER JOIN room r USING (roomid)
    LEFT JOIN price p ON rb.roomid = p.roomid 
        AND rb.bookingdate BETWEEN p.start_date AND p.end_date
WHERE rb.bookingid = 123
ORDER BY bookingdate;

giving

+-----------+------------+-------------+-------+
| bookingid | room_title | bookingdate | price |
+-----------+------------+-------------+-------+
|       123 | Geranium   | 2014-02-01  |    50 |
|       123 | Geranium   | 2014-02-02  |    60 |
|       123 | Geranium   | 2014-02-03  |    60 |
+-----------+------------+-------------+-------+