Hoping someone can help me out. I am building a calendaring program for items with php/mysql. Each item will have an entry into a table, listing a starting unix timestamp and an ending unix timestamp.
What I'm trying to do is build a query that will search all entries for a given item, and return rows that conflict with a given starting and ending timestamp.
So, for example, if I want to put something on the calendar to start on 12/10/08 @ 10am and end on 12/12/08 @ 8pm (using unix timestamps instead, of course), I want to first check the table to look for any previously scheduled slots that:
- begin before, but end during
- begin during
- are completely enveloped
does that make sense? It should also be noted that an event can end at the same time an event begins and vice-versa (ie event 1 can end at 10am and a new one can begin at 10am without conflict)but there cannot be overlap beyond that.
Here is my table layout, if that will help:
CREATE TABLE `schedules` ( `scheduleID` int(11) NOT NULL auto_increment, `itemID` int(11) NOT NULL, `forumID` int(11) NOT NULL, `statusID` tinyint(3) unsigned NOT NULL, `startingTimestamp` bigint(20) NOT NULL, `endingTimestamp` bigint(20) NOT NULL, `detailNotes` varchar(255) NOT NULL, PRIMARY KEY (`scheduleID`), FOREIGN KEY `itemID` (`itemID`) REFERENCES `items`(`itemID`), FOREIGN KEY `statusID` REFERENCES `schedule_status_types`(`ID`) ) TYPE=InnoDB;
Any help would be apprciated :) Been racking my brain for a while on this...should probably take a nap.