how to query only results that match over a number of days...

[dico]

Hello,

How do I write a query to do the following:

A person is searching for a "hotel room". They're going to be in town for 3 days. We have a DB full of rooms from various properties. Many properties have more than one room available. Some have availability for day 1 and day 3, some for day 2 and day 3 -- and some for all three days. Some have availabilty for all 3 days -- but in different rooms.

I need to find only the rooms that are available for 3 continous days -- so that the guest doesn't have to move rooms.

Right now something like this returns everything available -- not just the rooms available all three days.

$sql="SELECT room FROM inventory WHERE date>$start_day AND date<$end_day"

Can anyone please do up a quick sample query for me?

Thanks,

-dr

[bogu]

U know how to find witch rooms are not available (assuming that field named date represents the one that the room is not available), so u can do it by using 2 selects:

/*find the unavailable rooms*/
SELECT 
	GROUP_CONCAT(DISTINCT CONCAT('\'', room, '\'') SEPARATOR ',') as unavailable
FROM `inventory` 
WHERE `date` BETWEEN (CURDATE() - INTERVAL 3 DAY) AND CURDATE()
/*this concat CONCAT('\'', room, '\'') is not necessary if the room field is an integer
  INTERVAL 3 DAY - is just an example u can change that to the numbers of days u want
*/

put the result in a variable lets name the var $unavailable

/*grab all the rooms that are available*/
SELECT
	room
FROM `inventory`
WHERE room NOT IN ($unavailable)

[dico]

I should say that I'm using PHP and MySQL.

[Sxooter]

dico wrote:

I should say that I'm using PHP and MySQL.

Hehe, I was just about to suggest using "overlaps" in postgresql. haha. just kidding.

Seriously though, you can take that first query our fried gave you and add a NOT before the between to get what he was showing with the "not in ()" thingie.

WHERE `date` NOT BETWEEN (CURDATE() - INTERVAL 3 DAY) AND CURDATE()