I want to check for a date and time conflict using mysql and php.
My timestamp is formated using php code such as:
$timestamp_start = date("Y-m-d H:i:s", mktime(date("H", strtotime($start_time)), date("i", strtotime($start_time)), 0, $date[0], $date[1], $date[2]));
$timestamp_end = date("Y-m-d H:i:s", mktime(date("H", strtotime($end_time)), date("i", strtotime($end_time)), 0, $date[0], $date[1], $date[2]));
And I do mysql check like this below:
$sql = "SELECT * FROM `$info[table]` WHERE ((`timestamp_start` BETWEEN '$timestamp_start' AND '$timestamp_end') OR (`timestamp_end` BETWEEN '$timestamp_start' AND '$timestamp_end'))";
Which is code I got from here! haha.
Now the issue is that if the room is scheduled for 10/31/2013 from 08:00 AM to 05:00 PM and someone else tries to schedule it for 10/31/2013 from 5:00 PM to 09:00 PM they get a message stating that the room is already in use. Now I didnt mind this at first because than it forced the room to have atleast a 30 minute gap between events, but my bosses said that the room should be able to be scheduled back to back.
How can I modify my sql statement to allow someone to request the room at 05:00 PM if it is booked all the way to 05:00 PM?
This seems to work
$sql = "SELECT * FROM `$info[table]` WHERE `approved`='true' AND (`timestamp_start` < '$timestamp_end' AND `timestamp_end` > '$timestamp_start')";
BETWEEN is inclusive operation, e.g.
foo BETWEEN bar AND baz
is the equivalent of
(foo >= bar) AND (foo <= baz)
Since you want to allow "overlap" on the cutoff times, you'll have to ditch the `BETWEEN operation, and go for the more tedious-to-write-out
(foo >= bar) AND (foo < baz)
^--- non-inclusive
Found this on another question asked here:
$sql = "SELECT * FROM `$info[table]` WHERE `approved`='true' AND (`timestamp_start` < '$timestamp_end' AND `timestamp_end` > '$timestamp_start')";
And it appears to be working using the limited testing I did.