CODEWITHSUNDEEP
mysql
user2220551
user2220551

Reputation: 53

Check for a date/time conflict MySQL

OK... So I have a calendar, in a custom PHP application built using CodeIgniter, which pulls its entries from a MySQL database. In the table containing the entry data, there are 3 fields that govern the date (start_date), time (start_time) and duration (duration) of the entry. Now, as the end-user moves an entry to a new date and/or time, the system needs to check against these 3 fields for any schedule conflicts.

Following are some vars used in the query:

$request_entry_id // the id of the entry being moved
$request_start_date // the requested new date
$request_start_time // the requested new time
$request_duration // the duration of the entry being moved (will remain the same)
$end_time = ($request_start_time + $request_duration); // the new end time of the entry being moved

My query used to check for a schedule conflict is:

SELECT t.id
FROM TABLE t
WHERE t.start_date = '$request_start_date'
AND (j.start_time BETWEEN '$request_start_time' AND '$end_time'))
AND t.id <> $request_entry_id

The above query will check for any entry that starts on the same date and time as the request. However, I also want to check to make sure that the new request does not fall within the duration of an existing entry, in the most efficient way (there's the trick). Any suggestions? Thanks in advance!

Upvotes: 1

Views: 4408

Answers (1)

Joni
Joni

Reputation: 111219

It's easier to figure out the logic if you first think about the condition for when there is no conflict:

The new event ends before the existing one starts, or starts after the existing event ends.

For there to be a conflict we take the negative of the above:

The new event ends after the existing one starts, and starts before the existing event ends.

In SQL:

SELECT t.id
FROM TABLE t
WHERE t.start_date = '$request_start_date'
AND ('$end_time' > t.start_time AND '$request_start_time' < addtime(t.start_time, t.duration))
AND t.id <> $request_entry_id

Upvotes: 3

Related Questions