date comparison

[johnyen]

i would be greatful for any help

this is what im doing:-
a web site for a hotel

when a new guest goes to book a room they enter the arrive date and depart date, i need to do an sql query that will pull back the rooms available for the dates entered and to exclude rooms booked between the dates entered.

would anyone know how i would go about this ???

for this i have a table called "room_info" with the following cols
room_id (primary key)
room name
description
cost id

and a table called "bookings" with the following cols
booking_id (primary key)
room_id
arrive_date
depart_date
duration

thank u in advance

john.

[nnichols]

You can do this with a subselect but I am assuming that you are using MySQL which doesn't currently support them so you will need to do 2 queries. Try something like this -

<?
/*
  assuming that you have the following input variables
  $arrive = $_POST['arrive_date'];
  $depart = $_POST['depart_date'];
*/

// get list of rooms not available for the requested dates
$sql = "SELECT DISTINCT(room_id)
	FROM  bookings
	WHERE '$arrive' <= arrive_date AND arrive_date < '$depart'
	OR '$arrive' < depart_date AND depart_date <= '$depart'";
$res = mysql_query($sql, $db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);
while($row = mysql_fetch_array($res)) {
	// add this room_id to the array
	$room_id_array[] = $row[0];
	}
// turn array into comma separated list for use in 'IN' clause
if (is_array($room_id_array)) {
	$room_ids = implode(',', $room_id_array);
	}
else {
	$room_ids = 'NULL';
	}

// get list of rooms available
$sql = "SELECT *
	FROM  room_info
	WHERE room_id NOT IN ($room_ids)";
$res = mysql_query($sql, $db) or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);
while($row = mysql_fetch_array($res)) {
	// echo the available rooms
	}

?>

I haven't tested it but it should work 😉