Checking overlapping times against values in database

Question

I'm making a room booking system where the user enters times to book. The input is all done by clicking boxes, this part is just a check to make sure people don't tamper with the GET values to overwrite things. Here's what I have so far:

$Username = mysql_real_escape_string(($_POST['Username']));
$DateBooked = mysql_real_escape_string(($_POST['DateBooked']));
$Room = mysql_real_escape_string(($_POST['Room']));
$StartTime = mysql_real_escape_string(($_POST['StartTime']));
$EndTime = mysql_real_escape_string(($_POST['EndTime']));

$query="SELECT bookingid,StartTime,EndTime
        FROM bookings
        WHERE DateBooked = '$DateBooked' AND Room='$Room' AND Approved = 1";
$result=mysql_query($query);
$num=mysql_num_rows($result);

$i=1;
while ($i <= $num)
{
    $MinValue=mysql_result($result,$i,"StartTime");
    $MaxValue=mysql_result($result,$i,"EndTime");
    if ((($StartTime >= $MinValue) && ($StartTime <= $maxValue)) ||
            (($EndTime >= $MinValue) && ($EndTime <= $maxValue))) {
        $overflowed=true;
    }
    $i++;
}

if ($overflowed)
{
//Error message
}
else
{
//Save to database
}

My problem is the following:

14:00-16:00 saved in database
Attempt to book 13:00-15:00: Error given correctly.
Attempt to book 15:00-17:00: No error given.

What am I doing wrong?

Thanks!

Could it be the `$MaxValue` and `$maxValue` inconsistency in your code? — rtcherry, May 29 '13 at 23:07
You could easily query for a count of overlapping entries in a single query if the date/time is stored as an actual datetime or timestamp. With two events, A and B, you can find overlaps with just `startA <= endB AND startB <= endA`. That checks for all 5 overlap conditions. — Jonathan Kuhn, May 29 '13 at 23:14
If you can switch to Postgres, use an exclude constraint on the table (and the tsrange type), which does exactly what you want at the DB level: http://www.postgresql.org/docs/9.2/static/sql-createtable.html with an example at http://www.postgresql.org/docs/9.2/static/btree-gist.html — Denis de Bernardy, May 30 '13 at 00:14
Please do not use `mysql_*` functions anymore. They are no longer maintained and are [**deprecated as of PHP 5.5.0**](https://wiki.php.net/rfc/mysql_deprecation). Instead, use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) and learn about [_prepared statements_](http://en.wikipedia.org/wiki/Prepared_statement). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) can help you decide which MySQL API to use. — PLPeeters, May 30 '13 at 08:09
@JonathanKuhn They are stored as integers, from 10 (am) to 24 (midnight). How would I structure the query to do that? And what do you mean by "5" conditions? — Dan Hughes, May 30 '13 at 16:39

score 0 · Accepted Answer · edited May 23 '17 at 12:21

0

You have a few issues:

First: Your have inconsistent variables $MaxValue and $maxValue.

Second: Row numbers for mysql_result start at 0.

Third: Your comparison is incorrect.

You should change your loop to this:

$i=0;
while ($i < $num)
{
    $MinValue=mysql_result($result,$i,"StartTime");
    $MaxValue=mysql_result($result,$i,"EndTime");
    if (($StartTime < $MaxValue) && ($MinValue < $EndTime)) {
        $overflowed=true;
    }
    $i++;
}

Edit: You could improve the code in other ways too, for example you could stop the while loop once you've determine there is an overlap. Also, you may not want to loop over every request for a given room on that day. You could have the SQL query return approved requests whose times are in the same window as the new request (allowing you to drop the while loop).

Edit 2: Here is more information about overlapping dates.

edited May 23 '17 at 12:21

Community

1
1

answered May 29 '13 at 23:58

rtcherry

4,840
22
27

1) Woops, forgot about caps! Thanks for that one, should've noticed. 2) Again, thanks for the tip. 3) Would this not just check if the entire time is within the existing time? So if a stored time is, say, 10:00-16:00, and I attempt to book 13:00-15:00, this would not trigger an error as $MinValue is not < $EndTime? Unless I'm misunderstanding.. – Dan Hughes May 30 '13 at 10:59
I should explain, I'm trying to get it so that if either $StartTime or $EndTime are between $MaxValue or $MinValue, an error is triggered. – Dan Hughes May 30 '13 at 11:08
Should probably tag you! :-P @rtcherry – Dan Hughes May 30 '13 at 11:19
@DanHughes Yes I know. `($StartTime < $MaxValue) && ($MinValue < $EndTime)` is checking to ensure the submitted time is either completely before, or completely after each time pulled from the database. See the link in **Edit 2** for a more thorough explanation as to why what I provided works. If you used a SQL query to return approved requests whose times are in the same range, the error condition would be getting one or more results, and if no results are returned you know you can submit the new request. – rtcherry May 30 '13 at 17:57
Ah I see! There was another fix I needed to add to the previous page (EndTime was not being sent from form if the time overlapped), now it works perfectly! Thank you for your help. @rtcherry – Dan Hughes May 30 '13 at 20:32
@DanHughes i tried the above code but it's not working in my case where i'm doing similar to what you have done. Please Help – AK_56 Jun 05 '15 at 09:41

Checking overlapping times against values in database

1 Answers1