SQL statements selecting rows

Question

ok so I am trying to select rows from DB to see if there are any clashes within the calendar and I have come across another issue

here is my code

$query3 = $dbh->prepare("SELECT * FROM calendar_" . $companyID . " WHERE (calTime+duration) > :calstart AND (calTime+duration) <= (:calend + (duration-(:calend-calTime))) AND agentID = :agentID ORDER BY calTime ASC");
$query3->bindParam(':calstart', $time);
$calend = $time + $duration;
$query3->bindParam(':calend', $calend);

which looks something a like this in more readable SQL

  SELECT * FROM calendar_01 WHERE (calTime + duration) > 1331386200 
  AND (calTime + duration) <= ( 1331388000 + ( duration - ( 1331388000 - calTime ) ) )
  AND agentID =1
  ORDER BY calTime ASC
  LIMIT 0 , 30

the lower boundary obviously works fine however with the upper boundary I have the problem where if the duration of an event is 7200 2hrs and someone tries to enter a new event that is say only 1800 in length and starts 1800 after the first it will allow it even though the event that has been entered is 7200 long and would in fact overrun the new event. Therefore I need the upper boundary to be dynamic in the sense that if :calend < (calTime + duration) then select row otherwise use :calend as the upper boundary.

I though I was almost right with what I was attempting however this part

  ( 1331388000 + ( duration - ( 1331388000 - calTime ) ) )

ends up minus minus on some occasions therefore the upper boundary is much to large and includes rows when they should not be

I'm sorry, but what is your question? – Ben Mar 11 '12 at 00:15 — Ben, Mar 11 '12 at 00:15

score 2 · Accepted Answer · edited May 23 '17 at 10:24

It appears that you are looking to see whether a new entry overlaps at all with any existing entry. The new entry is specified by a start time (1331386200 in your example), and a duration (1800, inferred from commentary and 1331386200 + 1800 = 1331388000).

There are a number of questions on SO (including Determining whether two date ranges overlap) that deal with overlapping time periods, but the basic technique for determining whether two periods, denoted by [s1, e1] and [e2, s2] overlap is simple:

if (s1 < e2 && s2 < e1)
    ...the intervals overlap...

You can juggle with < vs <= if you need to. You can add extra criteria to your query as needed, of course.

Applying that to your example, it appears that you should be writing something like this:

-- s1 == calTime
-- e1 == calTime + duration
-- s2 == 1331386200
-- e2 == 1331388000

SELECT *
 FROM calendar_01
WHERE calTime    < 1331388000            -- s1 < e2
  AND 1331386200 < (calTime + duration)  -- s2 < e1
  AND agentID = 1                        -- other criteria
ORDER BY calTime ASC
LIMIT 0 , 30

If that returns any data, then there is a conflict between the new data and one or more existing rows of data (the statement will return a list of up to the first 30 such rows).

Thank you very much worked perfectly again got stuck over thinking things when there is usually a very simple answer! — Ash, Mar 11 '12 at 11:40

SQL statements selecting rows

1 Answers1