Am having a bit of a problem trying to work something out.
Imagine there is a table like so TIMESPAN(ID, start, end) where ID is an auto incrementing integer (which is irrelevant), start and date are MySQL date field types.
TIMESPAN can contain one or more records.
Now, imagine a user wants to come along and add another TIMESPAN with a start and end date. I need a way of making sure that these date sets do not overlap (so, the start date cannot be inbetween the start and end of a singleTIMESPAN in the database, the end date cannot be between the start and end date of a single TIMESPAN, so both start and end could fit in the middle of the end of one and the beginning of another, but one cannot start inbetween two, and finish during the second etc.
I've been trying for a while to think up a QUERY that will do either of the following: return 0 records, as the date is OK or return 1 or more records as the date is OK (returning 1 or more or 0 respectively if it is not OK), to determine in PHP whether the next can be valid.
Could anybody help me out of my problem?