Createing a bookings system

[jonniejoejonson]

I am trying to create a bookings system for a hotel...

 

Would you create a field for every possible date.. therefore having a table with 365 fields for 2011?... then if it was booked have a bookingId that would join to another table with that booking data?

 

regards J

[kickstart]

Hi

 

Not sure why you would do that.

 

Should be possible to have a query which will generate a list of dates, and cross join that with the rooms, and left join the result if that with a table of actual bookings.

 

All the best

 

Keith

[jonniejoejonson]

Thanks Keith, so how would you create a...

query which will generate a list of dates,?

thaks for your help J.

[kickstart]

Hi

 

Firstly, set up a table with 1 column 10 rows, with values from 0 to 9. In this case call it integers and the column name of i.

 

Then if you do the following you can select a range of numbers (joining the table against itself repeatedly). This gives you every number from 0 to 999, and the HAVING clause reduces it to 0 to 365.

 

SELECT a.i *100 + b.i *10 + c.i AS SomeNumber
FROM integers a, integers b, integers c
HAVING SomeNumber <=365

 

You can then add that number of days to a start date to get a range of dates. For example:-

 

SELECT DATE_SUB( '2011-01-01', INTERVAL a.SomeNumber DAY )
FROM (SELECT a.i *100 + b.i *10 + c.i AS SomeNumber
FROM integers a, integers b, integers c
HAVING SomeNumber <=365)a

 

All the best

 

Keith

[jonniejoejonson]

Thanks Keith, i will take a look at that.. kind regards J.