I have 2 databases. One is properties and the other is dates. In dates I have associated the land_id and a date (In YYYYMMDD format) which means that the date is not available.
I need to formulate a query that a user can specify a start and end date, and then choose a property for which dates are available (not in the date database). How do airline and hotel websites do this kind of logic? I was thinking about taking the date range and picking all days in between and doing a query where the dates do not match and ordering it by number of results, but I can see how that could easily turn into an intense query.
CREATE TABLE IF NOT EXISTS `dates` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`land_id` int(11) NOT NULL,
`date` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=44 ;
--
-- Dumping data for table `dates`
--
INSERT INTO `dates` (`id`, `land_id`, `date`) VALUES
(43, 1, '20100526'),
(39, 1, '20100522'),
(40, 1, '20100523'),
(41, 1, '20100521'),
(42, 1, '20100525');
