[RESOLVED] Checking bookings do not overlap

[phpSimon]

Hi all

I'm saving bookings to a table with a propertyID column, startDate and endDate.

Once a user has completed a form, and I've validated the requested from and to dates, and checked the to date is before the from date, I need to check the requested dates do not overlap any existing bookings for that property ID.

Is there an elegant way to do this? I've started with:

$startdate = YYYY-MM-DD (from a POST)
$enddate = YYYY-MM-DD (from a POST)

$query = "SELECT startDate AS bookedStart
               , endDate AS bookedEnd
            FROM bookings
           WHERE propertyID = '1'";
$result = mysql_query($query)or die('Error getting bookings');

while(list($bookedStart, $bookedEnd) = mysql_fetch_array($result)){

if ($startdate <= $bookedStart && $enddate <= $bookedEnd){  // overlaps the start
}

if ($startdate >= $bookedStart && $enddate <= $bookedEnd)} // overlaps the end
}

}

Is there a better way of doing this? Perhaps by asking MySQL for a result based on the overlap requirements, or a neater way in PHP?

Thanks

[cretaceous]

you can do it all in the SQL, something like:

$query = "SELECT startDate AS bookedStart
               , endDate AS bookedEnd
            FROM bookings
           WHERE startDate <'$enddate' AND endDate >'$startdate' "; 

you may need >= comparisons but above should get you going

there's also BETWEEN to use with two dates in SQL, but I don't think that will fit the requirement here

[phpSimon]

Thank you, that's really helpful. The overlap is much easier to check in SQL than in PHP, obviously!

[phpSimon]

I completed this task in SQL, using;

$query = "SELECT startDate AS bookedStartDate
			   , endDate AS bookedEndDate
			FROM bookings
		   WHERE ('$startdate' BETWEEN startDate AND endDate
		      OR  '$enddate' BETWEEN startDate AND endDate)";

Thank you for your suggestions, which helped me find the easiest way to do this!

[cretaceous]

you're welcome !
- now mark the thread resolved under 'thread tools'