MySQL Query, Date Range From "Blacklist"

Question

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

Answer 1

Having the Dates as VARCHAR will make it harder to write a query. I suggest to save the dates as DATE and use the following query. This should help:

SELECT * FROM dates WHERE date NOT BETWEEN '<start_date>' AND '<end_date>'

It selects the dates not being between the given range.

I found a solution in the MySQL 5.1. reference in order to make it work with VARCHAR,too:

SELECT STR_TO_DATE(date,'%Y%m%d') as real_date 
FROM dates WHERE real_date NOT BETWEEN '<start_date>' AND '<end_date>'

Or

SELECT DATE(date) as real_date 
FROM dates WHERE real_date NOT BETWEEN '<start_date>' AND '<end_date>'

btw: <start_date> and <end_date> are placeholders. Insert the concrete dates there (in format YYYY-MM-DD).