Note: This post turned out longer than I expected
You can skip to the code part at the bottom if you'd like
Basically you just have to check if the start time or the end time is withing the range of another reservation. That is:
Lets call:
S = start time (from the database)
E = end time (from the database)
nS = new start time (the time you want to check)
nE = new end time (the time you want to check)
Now, if nS is between S and E (nS >= S AND nS <= E)
OR
nE is between S and E (nE >= S AND nE <= E) )
You have found a collision.
However, I think I've come up with a more effective way. I'll try to guide you through my thinking, step by step. Hopefully it will make sence.
Lets say we have two reservations, an existing one and a new one that we want to check if it collides.
Now lets say that we let our friend check for us, and he
guarantees to 100% that the new one does not collide with the existing one.
That means we KNOW that the new one is either before or after the existing one.
That is, it either starts and ends before the existing one starts. Or, it starts after the existing one ends.
So with the names above, that means (nE <= S OR nS >= E).
Now we are able to check if it does not collide. But what you want to check is if it
does collide. So we negate it with an exclamation mark (!).
if
!(nE <= S OR nS >= E) is true, you found a collision.
Then there is actually a law for these kinds of logical expression. It may be tricky to really understand this last part, but that expression is the same as (nE > S AND nS < E). Which is a bit easier to write.
This check does require that nS < nE though. That is, that a reservation doesn't end before it starts. And that your fields are of a date type, so that you can compare dates with the < and > operation.
So, as a summary and the final awnser to your question: try this
PHP Code:
function CheckExistenceReservation($reserveDate,$StartTime,$EndTime,$MychapelID){ //2011-04-08
$sql_1="SELECT COUNT(res_date) AS MT FROM reservation "
. "WHERE res_date='$reserveDate' AND chap_id=$MychapelID "
. "AND '$EndTime' > res_startTime AND '$StartTime' < res_endTime";
$result=mysql_query($sql_1) or die (mysql_error());
return mysql_result($result,0,'MT');
}
