Forum Moderators: open
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!
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
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
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
will investigate. thanks for pointers :)
