Check between times for appointment..

[Fog Juice]

Using MySQL Version: 5.0.45

 

Hey everyone, I have a table that stores appointments for various things, it stores the time an appointment starts at, the time it ends, the date its on, titles, and a few other things. When people book appointments through my php script, the script checks to make sure that appointments don't overlap each other, so for example, if you booked an appointment from 10:00AM to 11:00AM and then tried to book from 10:30AM to 12:00PM, it would say that you already have an appointment booked during that time. I've been using PHP to do a check for this but i'm wondering if there's a way to do it with mysql.

 

Here is what I've started below but the script below doesn't check to see if an appointment is being booked between times yet. Can someone give me an idea of how I might check with mysql? I basically just want it to return all the appointments that conflict with what is being searched.

 

 

 

                SELECT id, title, owner, time, end_time, date
                FROM appointments WHERE
	`date` = '2008-02-28' AND
	`owner` = '25' AND
	`appointmenttype` = '5' AND
	`appointmentsubtype` = '4'

 

 

Table:

CREATE TABLE `appointments` (
  `id` bigint(20) NOT NULL auto_increment,
  `owner_id` bigint(20) NOT NULL,
  `type_id` bigint(20) NOT NULL,
  `subtype_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `title` varchar(150) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=312 ;

 

Thanks in advance if anyone decides to help out.

[Fog Juice]

Here's a visual example:

 

Appointment 1: |-----|

Appointment 2:      |------|

 

See how they overlap? I want to prevent overlapping, so appointment 2 wouldn't be booked because it overlaps appointment 1, and I want to find all appointments that would overlap.

[fenway]

You can self-join and see if the ranges overlap.

[Barand]

see

 

http://www.phpfreaks.com/forums/index.php/topic,183811.msg822544.html#msg822544

[Fog Juice]

thanks, I think I figured it out..

 

Insert code (see above code to create table):

INSERT INTO `appointments` (`id`,`owner_id`,`type_id`,`subtype_id`,`date`,`time`,`end_time`,`title`) VALUES (NULL,'1','1','1','2008-02-28','13:00:00','13:00:01','Test');

 

 

Test to check if any appointments are between start and end time:

 

 

SELECT *
FROM appointments
WHERE `date` = '2008-02-28' 
AND (`time` = '12:30' || `end_time` = '15:30' || `end_time` BETWEEN '12:30' AND '15:30' || `time` BETWEEN '12:30' AND '15:30') ;

 

the above is the same as below right? I just want to make sure, I know below I think I made it a little redundant because it does checks both ways and it is probably unnecessary.

 

SELECT *
FROM appointments
WHERE `date` = '2008-02-28' 
AND ('12:30' BETWEEN `time` AND `end_time` || `time` = '12:30' || `end_time` = '15:30' || '15:30' BETWEEN `time` AND `end_time` || `end_time` BETWEEN '12:30' AND '15:30' || `time` BETWEEN '12:30' AND '15:30') ;

 

 

[Barand]

SELECT *
FROM appointments
WHERE `date` = '2008-02-28' 
AND '15:30' > `time` AND '12:30' < `end_time`