Conflicting Times

[The Little Guy]

What would a good way be to check if two times ranges conflict with each other?

 

For Example:

I am making a meeting room schedule where people can sign up to use our meeting room. So if someone signs up to have a meeting at:

12:00 - 14:00

 

Then someone else wants to use the meeting room, and they sign up for:

13:30 - 15:00

 

They should get an error because the meeting room has already been checked out during part of that time range.

 

Any suggestions on how I can do this?

[teynon]

$sql="SELECT * FROM table WHERE (start_time <= '{$requestStartTime}' AND end_time > '{$requestStartTime}') OR (start_time <= '{$requestEndTime}' AND end_time > '{$requestEndTime}')";
if (@mysql_num_rows(@mysql_query($sql) > 0) {
   echo "This meeting room is already reserved for this time period.";
}

[The Little Guy]

Hmmm... Ill give that a try.

[Maq]

Hmmm... Ill give that a try.

Without the error suppressors..

[The Little Guy]

Thanks!

 

This is my final result:

$avalible = (bool)$this->db->GetOne($q = "SELECT 1 FROM meeting_room WHERE ((start_time <= '{$start_time}' AND end_time > '{$start_time}') OR (start_time <= '{$end_time}' AND end_time > '{$end_time}')) and reserve_date = '$date'");
if($avalible){
    echo "This meeting room is already reserved for this time period.";
}

[The Little Guy]

after testing this some more, it still has some issues.

 

if I have a start/end time like this:

01:00 - 05:00

 

It will allow me to do this:

02:00 - 04:00

 

but that time slot is already taken.

 

Over lapping times like this:

12:00 - 14:00

13:30 - 15:00

 

Seem to work just fine.

Any suggestions on how to check times that are between two larger times?

[teynon]

You're not running the code the way I showed it to you.

 

When you run the code, you have to determine if it returns any values. (That's what the mysql_num_rows is for.)

 

I believe your code is either going to be always true, or true when its suppose to be false.

 

The way that sql is checking the dates is like this:

if "inputted start date" is in between database start date and end date, or "inputted end date" is in between database start and end date, then that time is NOT available. So if that query returns any rows, the result is not eligible for reservation.

 

--- I haven't looked into it a whole lot as I am reading this quickly and getting back to my project.

 

If that above statement is not true, you should verify that your variables are set properly. Easy way to do this is to echo the SQL. If it is working properly, then copy the SQL and modify the query in PHPMyAdmin until it is appropriate.

 

Finally, post the solution here, so others can see what the issue was.

[The Little Guy]

My query returns one if there is something, and nothing if there isn't anything. So, if there is 1+ results the query will return 1, then convert that to "true" if there is 0 results the query will return an empty set, and then convert that to "false".

 

my query is the same as yours, the only difference is, is that I skipped mysql_num_rows