根据数据库中的值检查重叠时间

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!

Answer 1

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.