Right idea but I think this will be more efficient (who knows try both - you have a lot of "OR"s which can force another query to run):
I'm assuming your Unit table is called "unit" and the primary key is "id" and this joins to bookings.unit_id
SELECT id FROM bookings left join unit ON id = unit_ID WHERE $arrivalDate < departureDate AND $departureDate > arrivalDate GROUP BY id HAVING count(unit_ID) > 0
This will return a list of Unit id's that are available. As soon as MySQL supports sub queries you could use "NOT IN" and drop the group by but basically we're looking for the unit_ID's of any reservations that are live any time between your $arrivalDate and $departureDate and then looking for unit.id that match 0 of them.
Good Luck, Frank
On Feb 12, 2004, at 2:23 PM, [EMAIL PROTECTED] wrote:
From: amanda kerdal <[EMAIL PROTECTED]> Date: February 12, 2004 6:07:38 AM PST To: [EMAIL PROTECTED] Subject: boooking: get available dates
trying to do an e-booking service for BBs.
In mysql db I've got a booking table with an arrivalDate and a departureDate field for each Unit booked. My Units are the Rooms and in the Unit table.
I want to check availability for every single unit.
$result_dates=mysql_query("SELECT unit_ID FROM bookings WHERE unit_ID!=0 AND(arrivalDate<=$arrivalDate AND $arrivalDate<=departureDate OR arrivalDate<=$departureDate AND $departureDate<=departureDate OR $arrivalDate<=arrivalDate AND arrivalDate<=$departureDate OR $arrivalDate<=departureDate AND departureDate<=$departureDate)");
Is right to proceed like that?
thanks for any piece of help
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php