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:
What am I doing wrong?
Thanks!
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.