MySQL Query, Date Range From "Blacklist"

Posted by erbaker on Stack Overflow See other posts from Stack Overflow or by erbaker
Published on 2010-05-18T21:51:04Z Indexed on 2010/05/18 22:00 UTC
Read the original article Hit count: 174

Filed under:
|
|
|

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');

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql