Thread: PHP MySQL help!

I have a booking calendar database with the following fields:

booking_id (foreign Key linking to Bookings table)
calendar_startdate
calendar_enddate
calendar_status



So the booking obviously takes up all the dates between the startdate and enddate and there can only be one booking on each date.

I then have a form for users to make a booking request with the fields "start date" and "end date" this checks the database to see if the date range entered (between startdate and enddate) is available or not.

But I am not too sure how to do it. Any help or suggestions would be great.

I assume they are all date fields in the database? Well, apart from the ID (which I guess is a tinyint? Or a number none the less).

Reading this tutorial would probably help a lot. You'll need to set up a counter between the first date and the last date to do what you want, adding to the day all the time:
PHP Tutorial - Date

So did you have any luck writing the PHP code?
(I wasn't sure if you are a php programmer already?)

One method would be to use the following query for each day

Code:

SELECT COUNT(*) 
FROM tablename 
WHERE calendar_startdate <= 2009-08-09 
AND calendar_enddate >= 2009-08-09

This will then fetch the number of bookings on that date, if there is a count of more than 1 then you know the date can not be booked.

I would also create seperate indexs on both date fields to speed up the search. The reason you do a COUNT(*) is because it is a LOT quicker to count the number of rows than it is to fetch data and use a mysql_num_rows, infact if you benchmark this for a year (365) it still takes less time than it does for mysql_num_rows to do the same task for a month!

working with date in mysql is always problem I prefer to use datediff function