TimeStamp, existence, php, mysql frustration!~ GAHH

[stangn99]

I'm trying to build a room booking web application with php+mysql. I'm close, but something keeps going wrong.

The way it works is this:

1. User logs in
2. User redirects to "booking" page, where they can choose from 1 of 2 rooms to book
3. there are drop downs for the following: Date, Time from, Time to
4. optional comments
5. user hits "SUBMIT" and depending on the ERROR CHECK result, either the room will be booked, or an error will be thrown.

Here is my problem (assuming date April 22nd 2010 is used):

If room [A] is booked from:
7am-9am and;
10am-1pm and;
1pm - 4pm

Everything is fine, because there are no intersections in time.

One there is an overlap in time, things get weird. Here is my code for checking and inserting the booking.

I think what's happening is that if there are more than 2 bookings in a single day, the error check just craps out and accepts anything.

*users selected the desired FROM and TO times from a drop down select, which records the time in "H:i:s" format (ie. 10:00:00).

p.s: i'm new to this and have been able to put this together with my little experience in php and lots of research online.

<?php 
	$userid = $_POST['userid'];
	$bookTimeFrom = $_POST['bookTimeFrom'];
	$bookTimeTo = $_POST['bookTimeTo'];
	$meetingroom = $_POST['meetingroom'];
	$bookdate = $_POST['bookdate'];
	$today = $_POST['today'];
	$comment = $_POST['comment'];
	
	$fullname =$_POST['fullname'];
	
	$conflict = false;

	
	$bookTimeFromcv = strtotime($bookTimeFrom);
	$bookTimeTocv = strtotime($bookTimeTo);
		
	$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.';
		$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');
	}
	else {
		
		echo "There is either a time conflict, or an invalid time selection. Please click OK to adjust time, or try another room"; 
	}
	
	
?>

[colweb]

To me it looks like the major problem is in the way you describe how it works.

3. there are drop downs for the following: Date, Time from, Time to

Does those drop downs show all the possible times for a given date or only the the times that are still available?
The way you describe it, the following is possible, customer A books room 1 from 10am to 1pm, customer B can still book room 1 from 7am to 4pm, creating a big conflict with customer A.

[stangn99]

To me it looks like the major problem is in the way you describe how it works. Does those drop downs show all the possible times for a given date or only the the times that are still available?
The way you describe it, the following is possible, customer A books room 1 from 10am to 1pm, customer B can still book room 1 from 7am to 4pm, creating a big conflict with customer A.

Hmmmmm. I see what you mean, but it does seem to partially work.

You know what? I'll try setting up a local server once I get home and allow access + link it up here. I think it would be best if you guys could actually see it live.

I don't think I'm going to accomplish anything by trying to describe this. hah!

[stangn99]

okay, so I didn't have much success getting the local host open to the internet.

I think I know what the problem is though. I think my code is only finding the intersection between 2 times, when really I need to check the intersection between ALL times for the specific day.

In other words, I believe this is the problem :

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

Any suggestions?

[colweb]

okay, so I didn't have much success getting the local host open to the internet.

I think I know what the problem is though. I think my code is only finding the intersection between 2 times, when really I need to check the intersection between ALL times for the specific day.

In other words, I believe this is the problem :

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

Any suggestions?

This part of your code

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

seems a bit strange to me. Correct me if I'm wrong, but it looks like $from_compare will be 7am or maybe 1pm and $to_compage is always 4pm (well not 4pm but converted to epoch).

[stangn99]

This part of your code

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

seems a bit strange to me. Correct me if I'm wrong, but it looks like $from_compare will be 7am or maybe 1pm and $to_compage is always 4pm (well not 4pm but converted to epoch).

I don't follow :(
$from_compare hold data being pulled from existing bookings in the database.... same with $to_compare.

Here is what it would like in phpMyAdmin

[B]id	userid	bookdate	        room	          bookTimeFrom	   bookTimeTo[/B]
01	aa1	        2010-04-23	fishroom	  08:00:00	      10:00:00
02     aa1	        2010-04-23	fishroom	  12:00:00	      14:00:00
03	st2	        2010-04-23	fishroom	  10:00:00	      12:00:00

[colweb]

The while loop is a loop. So the query will get 3 rows but inside the while loop the data from the last row goes to $from_compare and $to_compare. The first time the loop executes, $fom_compage will be strtotime("08:00:00") the second time strtotime("12:00:00") and finally it will exit the loop with strtotime("10.00:00").

[LethargicCoder]

It seems that your problem is that you are only checking the last record that is returned from the DB. You need to place your intersect testing inside the while() loop, dropping out on a failure.

[stangn99]

Thanks guys, i appreciate the feedback.
I think i understand what you mean by the look issue.

I'll try LethargicCoder suggestion and put my testing INSIDE the while look. I could be wrong, but I think i've already tried this with the same result.

I'll post back my findings.

Thanks!

[stangn99]

Okay...i just tried it out, and the same thing happens.

I put the error checking inside the while loop, with the same result.

I have a feeling it has something to do with the actual error check.

It shouldn't allow me to do this:

[img]http://www.daniweb.com/forums/attachment.php?attachmentid=14529&stc=1&d=1272037210[/img]