Dates and Times - in between ranges

[stangn99]

Hey guys...I've run into a little issue (noob here).

I’ve setup a little application for scheduling days + times in different rooms.

I’m trying to implement some sort of an error check which determines is someone has already been scheduled for a certain day, time(range), and certain room. For example:

USER A is scheduled for the following:
Room: happyroom
Day: 2010/01/01
Time From: 10:00am
Time To: 1:00pm

Now USER B should be RESTRICTED from scheduling the following, because there is an overlap in the TIME RANGE, on that day, for that room.
Room: happyroom
Day: 2010/01/01
Time From: 11:00am
Time To: 1:30pm

I thought this might work:

$query = mysql_query("SELECT * FROM schedata WHERE timeFrom BETWEEN '$timeFrom' AND '$timeTo' AND schedate='$schedate AND room='$room'") or die(mysql_error());

if(mysql_num_rows($query)) { // If the query returned any rows
        echo "<script>alert('The room has already been reserved for \"$schedate\" from \"$timeFrom\" to \"$timeTo\". Click OK to try another day/time'); location = 'book.php?userid=$userid';</script>"; 
        die; 
    }else {
    
    $query = "INSERT INTO schedata (ID, userid, schedate, comment, room, today, timeFrom, timeTo) VALUES ('','$userid', '$schedate', '$comment', '$room', '$today', '$timeFrom', '$timeTo')";

    mysql_query ($query) or die ('error updating database');
    }

But it doesn’t seem to work correctly.

Any suggestions on this?

All the data is being inputed in a form. Users select the TIME FROM and TIME TO from two separate drop downs.

Thanks

[wrivera]

Hey guys...I've run into a little issue (noob here).

I’ve setup a little application for scheduling days + times in different rooms.

I’m trying to implement some sort of an error check which determines is someone has already been scheduled for a certain day, time(range), and certain room. For example:

USER A is scheduled for the following:
Room: happyroom
Day: 2010/01/01
Time From: 10:00am
Time To: 1:00pm

Now USER B should be RESTRICTED from scheduling the following, because there is an overlap in the TIME RANGE, on that day, for that room.
Room: happyroom
Day: 2010/01/01
Time From: 11:00am
Time To: 1:30pm

I thought this might work:

$query = mysql_query("SELECT * FROM schedata WHERE timeFrom BETWEEN '$timeFrom' AND '$timeTo' AND schedate='$schedate AND room='$room'") or die(mysql_error());

if(mysql_num_rows($query)) { // If the query returned any rows
        echo "<script>alert('The room has already been reserved for \"$schedate\" from \"$timeFrom\" to \"$timeTo\". Click OK to try another day/time'); location = 'book.php?userid=$userid';</script>"; 
        die; 
    }else {
    
    $query = "INSERT INTO schedata (ID, userid, schedate, comment, room, today, timeFrom, timeTo) VALUES ('','$userid', '$schedate', '$comment', '$room', '$today', '$timeFrom', '$timeTo')";

    mysql_query ($query) or die ('error updating database');
    }

But it doesn’t seem to work correctly.

Any suggestions on this?

All the data is being inputed in a form. Users select the TIME FROM and TIME TO from two separate drop downs.

Thanks

are you checking am and pms as well? BETWEEN wont work with non numeric values

[stangn99]

are you checking am and pms as well? BETWEEN wont work with non numeric values

I see what you're getting at. Yes, i guess I would need to differentiate between am/pm. I'm assuming I will have to convert the time to another format to calculate the difference?

I looking to person the following actions:

1. _POST from the form and assign date, time, and room name to Vars
2. Check against database to see if there will be a conflict in the times (from previously entered times), on the selected day, for the selected room.
3. If no conflict, write to database
4. if conflict - put out an error message.

It seems so easy in my head, but I just can't get it to work. Been trying now for 2 days... hahaha

[wrivera]

Drop your Am/pm and change your num rows to
if(mysql_num_rows($query)>0)

you should be fine

[stangn99]

Drop your Am/pm and change your num rows to
if(mysql_num_rows($query)>0)

you should be fine

Thanks!
I'll give that a shot and will report back :)

[stangn99]

So it didn't work with my code, but I did find something similar online. I've adjusted it slightly to work for my needs.

IT WORKS! BUT... it doesn't seen to be able to determine if there is a conflict if there is a room booked for 30 minutes.

For example:

ROOM A
From: 8:00
To: 12:00

Than I try to book for the following:

ROOM A
From: 10:00
To: 2:00

The result is an error because there is a conflict!

However, If I try to do this:

ROOM A
From: 8:00
To: 12:00

And

ROOM A
From: 10:30
To: 11:00

It allows the reservation. It never finds a conflict if a room is being booked for only 30 minutes.

Any ideas? Here is the code:

$query = mysql_query("SELECT * FROM bookingdata WHERE bookdate='$bookdate'") or die(mysql_error()); // Select all the rows
while ($row = mysql_fetch_array($query)) {
$from_compare=$row;
$to_compare= $row;
}
$intersect = min($bookTimeTo, $to_compare) - max($bookTimeFrom, $from_compare);
if ( $intersect < 0 ) $intersect = 0;
$overlap = $intersect / 3600;
if ( $overlap <= 0 ):
echo 'There are no time conflicts. <br><br>';
else:
echo 'There is a time conflict where the times overlap by ' , $overlap , ' hours. <br><br>';
endif;
echo date("g:i", strtotime($bookTimeFrom)) . "<br>";
echo date("g:i", strtotime($bookTimeTo)) . "<br>";
?>

[wrivera]

So it didn't work with my code, but I did find something similar online. I've adjusted it slightly to work for my needs.

IT WORKS! BUT... it doesn't seen to be able to determine if there is a conflict if there is a room booked for 30 minutes.

For example:

ROOM A
From: 8:00
To: 12:00

Than I try to book for the following:

ROOM A
From: 10:00
To: 2:00

The result is an error because there is a conflict!

However, If I try to do this:
ROOM A
From: 8:00
To: 12:00

And

ROOM A
From: 10:30
To: 11:00

It allows the reservation. It never finds a conflict if a room is being booked for only 30 minutes.

Any ideas? Here is the code:

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

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

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

$overlap = $intersect / 3600;

//if ( $overlap <= 0 ):
//try this instead
if($from_compare>=$bookTimeFrom && $from_compare<=$bookTimeTo){
echo 'There is a time conflict where the times overlap by ' , $overlap , ' hours. <br><br>';
}else{
echo 'There are no time conflicts. <br><br>';
}

echo date("g:i", strtotime($bookTimeFrom)) . "<br>";
echo date("g:i", strtotime($bookTimeTo)) . "<br>";

[stangn99]

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

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

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

$overlap = $intersect / 3600;

//if ( $overlap <= 0 ):
//try this instead
if($from_compare>=$bookTimeFrom && $from_compare<=$bookTimeTo){
echo 'There is a time conflict where the times overlap by ' , $overlap , ' hours. <br><br>';
}else{
echo 'There are no time conflicts. <br><br>';
}

echo date("g:i", strtotime($bookTimeFrom)) . "<br>";
echo date("g:i", strtotime($bookTimeTo)) . "<br>";

It still doesn't seem to be working right.

With the edits above, it is unable to determine a conflict with 30 min bookings and some 1+ hr bookings.

This is a tricky one.

[stangn99]

If I add this

strtotime

to my TIME variable, it does detect 30 minute bookings, but than screws up the value within the $_POST time variable. It always ends up converting the value of the variable to 7:00.

I still think its a matter of converting the time into a different format, doing the math, and reconvert into an understandable time format.

[wrivera]

If I add this to my TIME variable, it does detect 30 minute bookings, but than screws up the value within the $_POST time variable. It always ends up converting the value of the variable to 7:00.

I still think its a matter of converting the time into a different format, doing the math, and reconvert into an understandable time format.

keep the code but convert your time to military.

[stangn99]

WTF?!?!

It seems to work now.

I added "strtotime" to $_POST.

resulting in

$bookTimeFrom = date("g:i", strtotime($_POST));

And it seems to work... detects the 30 minutes, overlaps, etc.

I'm going to try to add the INSERT statement and see what shows up in the DB.

[stangn99]

keep the code but convert your time to military.

Yes..i think that was the problem. I'll do some more testing and will report back if its a success (or failure). :)

THANK YOU

[stangn99]

Alright... its working!

I have to convert the time format, and convert it back again. I'm sure there is a better way to do this, but I'm not hardcore enough to know :)

$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($bookTimeTocv, $to_compare) - max($bookTimeFromcv, $from_compare);
		if ( $intersect < 0 ) $intersect = 0;
	
	$overlap = $intersect / 3600;
		$bookTimeFromcvb =  date("H:i:s", ($bookTimeFromcv));
		$bookTimeTocvb = date("H:i:s", ($bookTimeTocv));

	
	if ( $overlap <= 0 and $bookTimeFromcvb!=$bookTimeTocvb) {
		
		
		echo 'There are no time conflicts.';
	}
	else {
		echo "There is a conflict"; 
	}

It's working perfect.

Thanks for your help!