Logic MySQL Based Problem

[adamcoppard]

All,

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?

[r937]

could you please just confirm whether it's okay for the new row to have a span that fits inside of an existing row's span?

have a look at the following diagram --

              proposed        proposed
                start             end
                  |               |
1    start---end  |               |
                  |               |
2          start--|--end          |
                  |               |
3                 |  start---end  |
                  |               |
4         start---|---------------|---end
                  |               |
5                 |        start--|--end
                  |               |
6                 |               |  start---end

you're saying you want to prevent the proposed new row only in cases 2, 4, and 5, but ~not~ case 3?

[Kalon]

you need to test for

1) when newstart >= start and newstart <= end

2) when newend >= start and newend <= end

If either returns 1 or more rows then your new dates overlap with at least 1 set of existing start, end dates

[adamcoppard]

No, the spans must be totally independent, they can't fit inside of each other nor follow any of the pervious test cases. However, assuming start = 2010-10-15 and end = 2010-10-25 of record 1, and the start of record 2 = 2010-11-05. Record 3 could be 2010-10-26 start and 2010-11-04 end but NOT start 2010-10-16 and end 2010-10-23.

I will try Kalon's solution later, although managed to sort out my own that was much more complicated. Will that later and post my own solution later as do not have access to the file at the moment.

[r937]

okay, so you're confirming that in the diagram above, the only legitimate proposed new timespans you would accept are cases 1 and 6, as cases 2 through 5 all have some sort of overlap which is not allowed

so here's how you do it

run this query --

SELECT 1
  FROM timespan
 WHERE end > proposedstart  /* eliminates case 1 */
   AND start < proposedend  /* eliminates case 6 */

the purpose of this query is to find an overlap

if it returns anything, you cannot accept the proposed new timespan

if it returns no rows, that means your proposed new timespan is okay

[Kalon]

I will try Kalon's solution later,

forget what I suggested. r937's suggestion is better.

1) it is only 1 query

2) my suggestion doesn't take case 3 into account.

[adamcoppard]

Well, thank you very much!

It works as intended and I am sure I tried that combination last night! Just goes to show late night coding doesn't point out the bleeding obvious! Thank you for your suggestions, and as an addendum as my code I produced last night compared everything eight times to get an answer, and required pulling a list of all the terms to begin with I can say that it's safely been solved.