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

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

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

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

you should be fine

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 :)

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>";
?>

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>";

$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.

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.

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.

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.

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

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!