a simple hotel booking system

how to check availability?

[jamie]

hi,

currently i am selecting all booked dates from the database and using php to compare these with the dates the customer has requested. if any match, then the room is not available.

i do this outside the SQL select. i can't think of a way to do this all using MySQL?

e.g. the customer has selected 20070715,20070716,20071517 and the availability table has a hotel_id and a field called booked_dates, which is a comma separated list of dates with no availability: 20070602,20070716,20070915,20070916

is there a way to check any matches in the SQL select?

much appreciate the help!

[justageek]

You could do a select * from tbl where booked_dates like '%dates_here%'. You could use the comma in the like as well But I'm guessing it'll work just fine without it.

Now, I have to know, why aren't the dates and hotel ids in separate tables where you could have a lot better performance and options?

JAG

[jamie]

hi JAG,

thanks for suggestions.

if i had a search looking for 5 consecutive days, then that would be an SQL similar to

SELECT * FROM tbl
WHERE booked_dates LIKE %20070715%
OR LIKE %20070716%
OR LIKE %20070717%
OR LIKE %20070718%
OR LIKE %20070719%

if someone wanted 21 consecutive days, wouldn't that be a very slow query - especially over multiple hotels? i'll have to test.

regarding the structure:
the blocked dates is a table with only two fields:

hotel_id ¦ blocked_dates

the hotel_id references other tables with more hotel information.

[justageek]

if someone wanted 21 consecutive days, wouldn't that be a very slow query - especially over multiple hotels?

Yes...it would be.

You should break out the data into separate tables and relate them together. That is the way to get much better performance. Any time I see a delimiter in a data field I know there will most likely be trouble in a query somewhere :-)

JAG

[jamie]

understand!

will investigate. thanks for pointers :)