Re: boooking: get available dates

[Frank M Flynn <frank@xxxxxxxxxx>]

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, php-db-digest-help@xxxxxxxxxxxxx wrote:

> From: amanda kerdal <footy2008@xxxxxxxx>
> Date: February 12, 2004 6:07:38 AM PST
> To: php-db@xxxxxxxxxxxxx
> Subject: boooking: get available dates
>
>
> Hi everyone
>
> 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
>
> Mandy

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php