Overlap of time slot

Question

i have made a booking system where user can select time slot enter start & end time & save those values into MySql. Now i have found an issue where User A has booked time slot from 3-4. Now User B wants to book time slot 2.30-4.30. At present it is allowing User B to select the time slot which i don't want.Tried to code for it but not getting the desired result. Please Help!

<?php
session_start();
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="testmra"; // Database name

// Connect to server and select databse.
$conn=mysqli_connect($host,$username,$password) or die("cannot connect");

mysqli_select_db($conn,$db_name);
$sname=$_SESSION['usr_name'];


$room = mysqli_real_escape_string($conn, $_POST['txtrname']);
$name = mysqli_real_escape_string($conn, $_POST['txtname']);
$purpose = mysqli_real_escape_string($conn, $_POST['txtpurpose']);
$attendee = mysqli_real_escape_string($conn, $_POST['attendee']);
$date = mysqli_real_escape_string($conn, $_POST['txtdate']);
$btime = mysqli_real_escape_string($conn, $_POST['btime']);
$etime = mysqli_real_escape_string($conn, $_POST['etime']);

$query="SELECT starttime,endtime FROM bookingdetails WHERE date = '$date' AND room='$room' ";
$result=mysqli_query($query);
$num=mysqli_num_rows($result);

$i=0;
  while ($i <= $num)
  {
    $MinValue=mysqli_result($result,$i,"starttime");
    $MaxValue=mysqli_result($result,$i,"endtime");
   if (($btime < $MaxValue) && ($MinValue < $etime)) {
        $overflowed=true;
    }
    $i++;
   }

   if ($overflowed)
   {
       //Error message
      header("location:karma.php");
       echo '<script language="javascript">';
    echo 'alert(" Time Slot is overlapping.Change the time slot")';
    echo '</script>';
    }
   else
   {
      //Save to database
             $sql="INSERT INTO bookingdetails  (room,name,purpose,attendee,date,starttime,endtime,status_id)VALUES('$room','$name','$purpose','$attendee','$date','$btime','$etime','2')";
     }


    if (mysqli_query($conn,$sql))
      {
        echo "Record added";

        }
    else
    {
        die('Error: ' . mysqli_error());
    }
  ?>

You can do a single sql request to check if your time slot overlaps others: Check this answer http://stackoverflow.com/questions/2545947/mysql-range-date-overlap-check — Needpoule, May 27 '15 at 09:44
@MrShibby. Thanks for the link but the logic i tried is correct ? — AK_56, May 27 '15 at 10:01
Well instead of doing your request and then loop through the results, you can do the single request which count the overlapping bookings. If you find more than 0 then the user need to change his time slot. — Needpoule, May 27 '15 at 10:09
possible duplicate of [mysql - INSERT date range into date columns IF dates don't overlap with existing ones](http://stackoverflow.com/questions/30434839/mysql-insert-date-range-into-date-columns-if-dates-dont-overlap-with-existing) — Strawberry, May 27 '15 at 13:27

Needpoule · Answer 1 · 2015-06-04T07:14:55.723

1

You can perform a sql request that count existing overlappings:

SELECT count(*) WHERE $requested_start < endtime
AND $requested_end > starttime
AND date = $date
AND status_id = 2;

You can now use this result to check if the time slot is available or not:

$overlappings = mysqli_fetch_row($result);

// If there is no overlapping
if($overlappings[0] <= 0){
  // time slot is available
}

edited Jun 04 '15 at 07:14

answered May 27 '15 at 12:42

Needpoule

4,476
24
33

There's no need to check first, but if you are going to check first, then you need to do it as part of a transaction. – Strawberry May 27 '15 at 12:53
My mistake, using num_rows with the result of a count() will return 1. Let me edit my answer. – Needpoule Jun 04 '15 at 07:14
Sir just wanted to ask if i have written the code properly ? Because i used the above code it didn't inserted the value into db also. – AK_56 Jun 04 '15 at 08:46
With your current code $overlappings is always equals to 1 so your insert query will never be executed. – Needpoule Jun 04 '15 at 08:58
So what changes i need to do ? – AK_56 Jun 04 '15 at 10:11
Check my edit, try mysqli_fetch_row to fetch the result of the count query. – Needpoule Jun 04 '15 at 10:14

score 0 · Answer 2 · answered Jun 23 '15 at 09:19

0

<?php  
 $sql = "select room_no FROM roominfo WHERE room_no NOT IN (SELECT roomno FROM guestrocordtransac WHERE '".$format_checkin."' between checkin AND checkout and roomtype='".$roomtype."' UNION SELECT roomno FROM guestrocordtransac WHERE '".$format_checkout."' BETWEEN checkin AND checkout and roomtype='".$roomtype."') and roomtype='".$roomtype."' ";
 $retval = mysql_query( $sql, $conn );
 if(mysql_num_rows($retval)== 0 ||mysql_num_rows($retval)== null )
{
  ?>
 <script type="text/javascript">
alert("Please select another date/Type")</script>
 <?php
 }
 else{   ///inserrt code///}

This is a sample code ...Replace the format checkin with your time ...I think this might help you..

answered Jun 23 '15 at 09:19

user12688

71
1
11

Will Try this but confused. 1. Why have you used room_no & what does guestrocordtransac means – AK_56 Jun 23 '15 at 09:29
apart from start and end time in booking details what are the other fields do you have...do you have unique id there – user12688 Jun 23 '15 at 09:43
Sir u misunderstood it. This page is to check for overlap of time slot for a particular room only – AK_56 Jun 23 '15 at 09:44
SELECT starttime,endtime FROM bookingdetails WHERE room NOT IN (SELECT room FROM bookingdetails WHERE '".$btime."' between starttime AND endtime UNION SELECT room FROM bookingdetails WHERE '".$etime."' between starttime AND endtime ) AND room='$room' and date = '$date'"; you have to change ur code like this – user12688 Jun 23 '15 at 09:52
Ok Will try it .Thanks for the help. But do you think this will work out ? – AK_56 Jun 23 '15 at 09:56
just copy the code and try it in wampserver provide with dummy date and time...For me its not the time its about date...Thats the only reason i'm not sure of...other than that it works fine – user12688 Jun 23 '15 at 10:04

Overlap of time slot

2 Answers2