[SOLVED] Select Query

[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

[akitchin]

your calculation should be that either both proposed new booking times are less than the start time of the other, or greater than the end time:

 

WHERE request_date = '$request_date' AND
  ($s_time < s_time AND $e_time <= s_time) OR
  ($s_time >= e_time AND $e_time > e_time)

 

it's a bit redundant to check whether the proposed booking end time is greater than e_time in that second time clause, since if the starting time is after the end time of the other booking is sufficient (presuming $s_time < $e_time).

[zed420]

Thanks for your reply I've already tried what you've posted that gives you duplicate bookings the very thing I'm trying to avoid. 

Thanks Zed

[akitchin]

Thanks for your reply I've already tried what you've posted that gives you duplicate bookings the very thing I'm trying to avoid. 

Thanks Zed

 

what does your table look like? and how are multiple bookings handled with regards to s_time and e_time? do you simply extend them? what about gaps in the timetable?

[zed420]

Its a form with drop-down boxes of start time and end time A day booking are from 08:00 to 21:00 normally its 1hr a lesson but when its a test its 2hrs.  what I don't want is a duplicate of any lesson or Over lapping any lesson.

Zed

This is my table booking

CREATE TABLE booking (
  b_id smallint(5) NOT NULL auto_increment,
  `dateTime` char(15) NOT NULL,
  user_id smallint(5) unsigned NOT NULL,
  s_time varchar(10) NOT NULL,
  e_time varchar(10) NOT NULL,
  request_date varchar(20) NOT NULL,
  PRIMARY KEY  (b_id),
  KEY user_id (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

[akitchin]

Its a form with drop-down boxes of start time and end time A day booking are from 08:00 to 21:00 normally its 1hr a lesson but when its a test its 2hrs.  what I don't want is a duplicate of any lesson or Over lapping any lesson.

Zed

This is my table booking

CREATE TABLE booking (
  b_id smallint(5) NOT NULL auto_increment,
  `dateTime` char(15) NOT NULL,
  user_id smallint(5) unsigned NOT NULL,
  s_time varchar(10) NOT NULL,
  e_time varchar(10) NOT NULL,
  request_date varchar(20) NOT NULL,
  PRIMARY KEY  (b_id),
  KEY user_id (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

 

what do you mean by duplicate lesson - do you mean the same user_id on the same day? and how are you storing the times? in the XX:XX format? if so, you'd be wiser to store them as integers without the colon, as this allows for more straightforward calculations. you should also store your dates in DATE format, because this offers a lot more flexibility in how you SELECT your data.

[zed420]

Thanks everyone, Sorted at last this is the final out come

SELECT COUNT(*) AS Cnt

  FROM booking

WHERE request_date = '$request_date'

  AND $e_time > s_time

  AND $s_time < e_time