MySQL query and join not for the faint hearted

[chrispos]

I am using mysql 4.1 and php 4

 

I am making a hotel booking system. The date page has been built and is working. The start days for the individual holiday start date and finish date are worked out by the year day using the make date function.

 

I have two tables one for the hotel rooms with rid, description,

 

The next table is for the bookings bid, rid, dayofyear, dayofyear1

 

I have joined the two tables and used a for loop in php to check the start and departure dates of dayofyear and dayofyear1 This works fine.

 

I have a problem that in the bookings table every time a booking is put in the table it appears in the search ie room 1 appears twice because it is booked twice in March. What I am trying to do is join the tables which i have using the rid ie select (*) from `rooms` LEFT JOIN `bookings` ON rooms.id = bookings.id

 

That works fine but what I want to do is just select the available rooms from a given period.

 

If any one can help I would be over the moon

 

Thanks for your time this is my last hope

[Mchl]

PHP4 is outdated

MySQL4 is outdated

 

Just to make sure you know that

 

Ok.. so I assume, that `dayofyear` stores the actual day of year when the booking starts, and `dayofyear1` stores the day of year when booking expires...

 

Take a look at MySQL's date and time functions. Specifically DAYOFYEAR()

 

I would also suggest using a DATE type field instead of using a day of year.