We are working on a booking system that needs to support split appointments. These appointments have a first half, a break during which something else can be booked and then the second half.
The system also supports normal bookings which have a standard start and end.
We need to check the database to see if there are any existing bookings that overlap with a booking we wish to make.
We have done this in PHP before but need to do it solely in mysql in this situation.
startDate (always) splitStartDate (sometimes) splitEndDate (sometimes) endDate (always)
When the splitStartDate and splitEndDate are not being used for a booking they have a value of 0000-00-00 00:00:00
We started trying to build the mysql statement out of ifs, but it seems ridiculously long.
"SELECT *
FROM bookings WHERE
(
(
IF(
splitStartDate != "0000 00:00:00",
IF(
((CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= startDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < splitStartDate))
OR
((CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= splitEndDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < endDate))
, 1, 0
),
IF(
(CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") < endDate) AND (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") >= startDate), 1, 0
)
) = 1
)
OR
(
IF(
splitStartDate != "0000-00-00 00:00:00",
IF(
((CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > startDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= splitStartDate))
OR
((CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > splitEndDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= endDate))
, 1, 0
),
IF(
(CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") > startDate) AND (CONVERT_TZ('" . $requested_end_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\") <= endDate), 1, 0
)
) = 1
)
OR
(
We know there has to be a much simpler way to do this but have been stumped as to what it is. We'd love any suggestions on how to build a mysql query for this.
When checking the availability we have been running the following checks (Sorry for how long this is, but that's the problem):
------------
all simple
requested startDate is in-between existing startDate and existing endDate
requested endDate is in-between existing startDate and existing endDate
existing startDate is in-between requested startDate and requested endDate
existing endDate is in-between requested startDate and requested endDate
-------------
advanced desired / simple existing
requested startDate is in-between existing startDate and existing endDate
requested splitStartDate is in-between existing startDate and existing endDate
existing startDate is in-between requested startDate and requested splitStartDate
existing endDate is in-between requested startDate and requested splitStartDate
requested splitEndDate is in-between existing startDate and existing endDate
requested endDate is in-between existing startDate and existing endDate
existing startDate is in-between requested splitEndDate and requested endDate
existing endDate is in-between requested splitEndDate and requested endDate
------
simple desired / advanced existing
requested startDate is in-between existing startDate and existing splitstartDate
requested endDate is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested startDate and requested endDate
existing splitstartDate is in-between requested startDate and requested endDate
requested startDate is in-between existing splitEndDate and existing endDate
requested endDate is in-between existing splitEndDate and existing endDate
existing splitEndDate is in-between requested startDate and requested endDate
existing endDate is in-between requested startDate and requested endDate
-----
advanced both
1st 1/2 both
requested startDate is in-between existing startDate and existing splitstartDate
requested splitStartDate is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested startDate and requested splitStartDate
existing splitstartDate is in-between requested startDate and requested splitStartDate
2/2 desired 1/2 existing
requested splitEndDate is in-between existing startDate and existing splitstartDate
requested endDate is in-between existing startDate and existing splitstartDate
existing startDate is in-between requested splitEndDate and requested endDate
existing splitstartDate is in-between requested splitEndDate and requested endDate
1/2 desired 2/2 existing
requested startDate is in-between existing splitEndDate and existing endDate
requested splitStartDate is in-between existing splitEndDate and existing endDate
existing splitEndDate is in-between requested startDate and requested splitStartDate
existing endDate is in-between requested startDate and requested splitStartDate
2nd 1/2 all
requested splitEndDate is in-between existing splitEndDate and existing endDate
requested endDate is in-between existing splitEndDate and existing endDate
existing splitEndDate is in-between requested splitEndDate and requested endDate
existing endDate is in-between requested splitEndDate and requested endDate
Thanks so much
My guess is that if you cleanup the date manipulation stuff you're doing, you'll like your query.
Move it to a mysql variable:
SET @REQUEST = (CONVERT_TZ('" . $requested_start_time->format("Y-m-d G:i:s") . "', \"$tzone\", \"UTC\");
and then your code looks like this:
IF(
splitStartDate != "0000 00:00:00",
IF(
(@REQUEST >= startDate AND @REQUEST < splitStartDate)
OR
(@REQUEST >= splitEndDate) AND @REQUEST < endDate))
, 1, 0
),
IF(
(@REQUEST < endDate AND @REQUEST >= startDate), 1, 0
)
) = 1
Which is not unreasonable. Using "X = 1" looks like a where clause to me, so I'd prefer to see
select count(*)
from bookings
where
(
splitStartDate is not null
and (
(@REQUEST >= startDate AND @REQUEST < splitStartDate)
OR
(@REQUEST >= splitEndDate) AND @REQUEST < endDate))
)
)
OR
(
splitStartDate is null
and (
@REQUEST < endDate AND @REQUEST >= startDate
)
)
Good luck!