Booking system

[dmdb]

Hi people,

 

I'm trying to design an booking system and have a problem with preventing conflicting bookings for items. Keeping it fairly simple for the moment, if I have a table of items, so:

 

items.id | items.name

 

And then a table for booking items (ignoring users and other details for the moment, also assuming only one item per booking to keep things simple for the purposes of this question):

 

bookings.id | items.id | bookings.startdate | bookings.enddate

 

If say an item was booked for the 1st Jan through to 5th Jan, if someone then enters a booking including that item for 3rd Jan to 9th Jan for example, how would I run a check on the free and available dates for that item. As in the real scenario items can be booked for up to 3 weeks I'd have to pull from the bookings table all values which related to that item and then check each day individually. I know how to do some searches on an SQL database but how do I search for a value which isn't specifically mentioned (the dates inbetween the start and end date).

 

If anyone can help I'd very much appreciate it and I'm hoping I've explained well enough!