StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCheck for timestamp conflict in mysql using php
text
Body
copied!<p>I want to check for a date and time conflict using mysql and php.</p> <p>My timestamp is formated using php code such as:</p> <pre><code>$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])); </code></pre> <p>And I do mysql check like this below:</p> <pre><code>$sql = "SELECT * FROM `$info[table]` WHERE ((`timestamp_start` BETWEEN '$timestamp_start' AND '$timestamp_end') OR (`timestamp_end` BETWEEN '$timestamp_start' AND '$timestamp_end'))"; </code></pre> <p>Which is code I got from here! haha.</p> <p>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.</p> <p>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? </p> <p>This seems to work</p> <pre><code>$sql = "SELECT * FROM `$info[table]` WHERE `approved`='true' AND (`timestamp_start` < '$timestamp_end' AND `timestamp_end` > '$timestamp_start')"; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload