Select Query

[zed420]

Hi All
I've been struggling with this query for past 4days now see If anyone can help me please. A typical day's diary starts from 08:00 till 21:00, normally you would book an hr but just to test the script I booked 8am to 9pm so that means all day has been booked NO other booking can be taken for this day if I DO NOT select the varibles 8 as s_time & 21 as e_time IT WILL book again and again . ? why why?
Thanks Zed

$query = "SELECT b_id, COUNT(*) AS Cnt 
2.FROM booking 
3.WHERE request_date='$request_date' 
4.AND e_time > '$s_time' 
5.AND s_time < '$e_time' 
6.GROUP BY b_id"; 
7. $result = mysql_query($query); 
8. $row = mysql_fetch_assoc($result);
9. if($row['Cnt'] > 0){ 
10. error_message('Sorry, this time is already booked. Please choose another');
11. } else { 

[zed420]

Hi All
I've managed to get somewhere with the query below with some help but it has a small problem after rigorous testing I found there is a small glitch e.g. Let’s say if I book from 10:00 to 13:00 and someone tries to book it from 08:00 to 21:00 (all day) IT WILL BOOK, meaning it will over lap. I have tried to sort it but failing miserably, anyone with any thoughts
Thanks
Zed

SELECT b_id, COUNT(*) AS Cnt 
FROM booking 
WHERE request_date='$request_date' AND 
( 
 ($s_time >= s_time AND $s_time < e_time) OR 
 ($e_time > s_time AND $e_time <= e_time) 
) 
GROUP BY b_id

[deejay]

Seems to me there's FOUR possible ways a new booking is going to infringe on an existing (e) one:

New booking starts before e-start time, and finishes after e-start and before e-end time.

New booking starts before e-start time and finishes after e-end time.

New booking starts after e-start and before e-end time, and finishes before e-end time.

New booking starts after e-start and before e-end time, and finishes after e-end time.

If you phrase those four possibilities into your OR statement I would think you'd have it covered?