Finding INTERSECTION of time - Between 2 or more times! GOING NUTS!

[stangn99]

I'm working on making a simple room booking system w/ php+mysql.

 

Users select a date from a drop down box, select "time from" from a drop downbox, and "time to" from another drop down box.

 

For any given day, there can be 1 or more bookings. My code seems to work find if there are 2 bookings, but when there are more than 2 bookings things go wonky. For example, users will booking the following:

 

 

Date: April 23rd 2010

Meeting room: boardroom

                      9:00-11:00

                      12:00-1:00

                      ** At this point, if I try to book a room from 10:30-12:00, it won't let me because of the overlap. Now if do the following:

 

Date: April 23rd 2010

Meeting room: boardroom

                      9:00-11:00

                      12:00-1:00

                      3:00-4:30

                      and than add ANOTHER booking for:  8:00 - 11:00 it will allow it, even tho there is overlap

               

But, if I change 8:00 - 11:30, it will complain about an overlap in time.

 

 

WTF?!

 

Here is my code. Any help would be greatly appreciated. I'm willing to try anything at this point...so please don't be shy.  :'(

 

<?php 
$userid = $_POST['userid'];
$bookTimeFrom = strtotime($_POST['bookTimeFrom']);
$bookTimeTo = strtotime($_POST['bookTimeTo']);
$meetingroom = $_POST['meetingroom'];
$bookdate = $_POST['bookdate'];
$today = $_POST['today'];
$comment = $_POST['comment'];
$fullname =$_POST['fullname'];


$query = mysql_query("SELECT * FROM bookingdata WHERE bookdate='$bookdate' AND meetingroom='$meetingroom'") or die(mysql_error()); // Select all the rows


	while ($row = mysql_fetch_array($query)) {
		$from_compare= strtotime($row['bookTimeFrom']);
		$to_compare= strtotime($row['bookTimeTo']);
	}




$intersect = min($bookTimeTo, $to_compare) - max($bookTimeFrom, $from_compare);
	if ( $intersect < 0 ) $intersect = 0;

$overlap = $intersect / 3600;

echo $overlap;
	$bookTimeFromcvb =  date("H:i:s", ($bookTimeFrom));
	$bookTimeTocvb = date("H:i:s", ($bookTimeTo));


if ( $overlap <= 0 and $bookTimeFromcvb!=$bookTimeTocvb) {


	echo 'There are no time conflicts.+++++  $conflict <br><br>';
	$query = "INSERT INTO bookingdata (ID,userid,bookdate,comment,meetingroom,today,bookTimeFrom,bookTimeTo) VALUES ('','$userid','$bookdate','$comment','$meetingroom','$today','$bookTimeFromcvb','$bookTimeTocvb')";
	mysql_query ($query) or die ('error updating database');
	echo "<script>alert('Your meeting has been booked. Click OK to return to the main page.'); location = 'book.php?userid=$userid';</script>";
}
else {

	echo "<script>alert('There is either a time conflict, or an invalid time selection. Please click OK to adjust time, or try another room'); location = 'book.php?userid=$userid';</script>"; 
}


?>

 

 

 

p/s: this is a small project, and i'm pretty new to this. Please go easy on me

 

 

[cags]

If you stored bookTimeFrom and bookTimeTo as DATETIME then you could probably simply do the following to find interceptions.

 

SELECT * FROM bookingdata 
WHERE meetingroom = '$meetingroom' AND (bookTimeFrom BETWEEN '$from' AND '$to' OR bookTimeTo BETWEEN '$from' AND '$to')

 

Disclaimer: This is an untested theory.

[Psycho]

If you stored bookTimeFrom and bookTimeTo as DATETIME then you could probably simply do the following to find interceptions.

 

SELECT * FROM bookingdata 
WHERE meetingroom = '$meetingroom' AND (bookTimeFrom BETWEEN '$from' AND '$to' OR bookTimeTo BETWEEN '$from' AND '$to')

 

Disclaimer: This is an untested theory.

 

Yeah, that won't work. Example:

 

Existing entry of 8:00AM to 11:00AM

New Entry: 9:00AM and 10:00AM.

 

Neither of the existing begin/end times are between the new begin/end times. I recall this same type of question being asked a long time ago on these forums. I'll see if I can dig it up.

[Psycho]

Here is the correct logic

SELECT *
FROM bookingdata
WHERE meetingroom = '$meetingroom'
  AND bookTimeFrom < '$to'
  AND bookTimeTo > '$from'

 

It first checks if the any existing meeting has a start time that is before the new meeting's end time (if they start after an existing meeting ends there is no conflict). Then IF an existing meeting does start before the new meeting it checks if that existing meeting ends after the new meetings begin time.

[stangn99]

Here is the correct logic

SELECT *
FROM bookingdata
WHERE meetingroom = '$meetingroom'
  AND bookTimeFrom < '$to'
  AND bookTimeTo > '$from'

 

It first checks if the any existing meeting has a start time that is before the new meeting's end time (if they start after an existing meeting ends there is no conflict). Then IF an existing meeting does start before the new meeting it checks if that existing meeting ends after the new meetings begin time.

 

MJ,

Would I need to alter my  min/max overlap check?

 

Sorry for the lame question. I'm really new to this and only was able to get this far with the help of forums, php..net, and random google searches.

 

Thanks so much for your help.

[cags]

The theory is the query will only returns rows that have an intersection. As such all you should have to do is check if mysql_num_rows returns a value greater than 0 after the query has been ran. If it does then there is a conflict.

[stangn99]

This is embarrassing. I'm trying to learn it though

 

Here is what I have now:

 

   

$query = mysql_query("SELECT * FROM bookingdata WHERE meetingroom = '$meetingroom' AND bookTimeFrom < '$bookTimeTo' AND bookTimeTo > '$bookTimeFrom'");

	while ($row = mysql_fetch_array($query)) {
		$from_compare= strtotime($row['bookTimeFrom']);
		$to_compare= strtotime($row['bookTimeTo']);
	}

$intersect = min($bookTimeTo, $to_compare) - max($bookTimeFrom, $from_compare);
	if ( $intersect < 0 ) $intersect = 0;

$overlap = $intersect / 3600;

echo $overlap;
	$bookTimeFromcvb =  date("H:i:s", ($bookTimeFrom));
	$bookTimeTocvb = date("H:i:s", ($bookTimeTo));


if ( $overlap <= 0 and $bookTimeFromcvb!=$bookTimeTocvb) {

 

Are you saying I should adjust the WHILE look to look something like this?

while ($row = mysql_num_rows ($query)) { -------------------- } 

?

 

If nothing comes up, proceed with the booking and if something does come up, stop the booking?

 

Could I use an IF statement within the WHILE loop to somehow check if anything was returned (like using  empty($row)) ?

 

 

Again... sorry i'm new to all of this. I've really enjoyed working on this little project and would love to hammer this last problem out

 

 

[cags]

Assuming the query is correct, it's just...

 

$query = mysql_query("SELECT * FROM bookingdata WHERE meetingroom = '$meetingroom' AND bookTimeFrom < '$bookTimeTo' AND bookTimeTo > '$bookTimeFrom'");

if(mysql_num_rows != 0) {
   echo 'conflict';
} else {
   echo 'no conflict';
}

[stangn99]

Alright... i'm going to try to implement this.

I'll be back in a few minutes with my results.. it might take me a while 

[stangn99]

Looks like its not going to work either.

 

No matter what time values I select, it always gives me a conflict. If I select a totally new date with no booking, it still gives me a "conflict".

 

This is the code i'm using:

    $query = mysql_query("SELECT * FROM bookingdata WHERE meetingroom = '$meetingroom' AND bookTimeFrom < '$bookTimeTo' AND bookTimeTo > '$bookTimeFrom'");
$numrow = mysql_num_rows ($query);

if($numrow != 0) {
	   echo 'conflict';
	} else {
	   echo 'no conflict';
	}

 

[Psycho]

Echo the query to the page and echo the results of the query to the page. Then analyze the data.

[stangn99]

Here is the ECHO of the Query itself:

SELECT * FROM bookingdata WHERE meetingroom = 'fishroom' AND bookTimeFrom < '1272029400' AND bookTimeTo > '1272020400'

 

I tried to echo the result of the query, but I only get:

Resource ID #4

 

 

if I echo the result using:

while ($row = mysql_fetch_array($query)) {
	echo $row['bookTimeFrom'];
	echo $row['bookTimeTo'];
}

 

I get 10:00:00      14:00:00

[Psycho]

As cags stated:

If you stored bookTimeFrom and bookTimeTo as DATETIME then you could probably simply do the following to find interceptions.

 

You are storing the values as a time format and the query is then comparing a time to a datetimestamp. I would suggest using datetimestamps for the start/end times in the database. Then you do not need the date field for the record since you can ascertain that from the start/end values.

[stangn99]

As cags stated:

If you stored bookTimeFrom and bookTimeTo as DATETIME then you could probably simply do the following to find interceptions.

 

You are storing the values as a time format and the query is then comparing a time to a datetimestamp. I would suggest using datetimestamps for the start/end times in the database. Then you do not need the date field for the record since you can ascertain that from the start/end values.

 

You are GOD. I haven't 100% got it working, but I think i'm EXTREMELY close. I'm going to do some more tinkering.

 

Re: timestamp... yes - this makes total sense. I'm going to try out just using timestamps vs. H:i:s

 

 

[stangn99]

Alas....SUCCESS.

 

I would like to thank you for your help in allowing me to successfully put this thing together.

 

It is working perfectly with timestamps and with your suggested MySQL query.

 

THANK YOU.