Hi All,

I'm totally stumped on this problem, I hope you can help.

I'm working on a vacation property rentals database and one of the forms allows the user to search for all properties that are available between x and y dates.

The bookings table stores the bookings for each property, with a start date and an end date.

I have two tables (I've reduced the number of fields to make this example easier):

property

*prop_id
name

Bookings

*booking_id
prop_id
date_from
date_to
user_id

I'm more used to SQLServer so my first attempt used the IN() function, something like...

SELECT prop_id, name from property WHERE prop_id NOT IN(SELECT prop_id FROM bookings WHERE (x BETWEEN date_to AND date_from) OR (y BETWEEN date_to AND date_from)

But my version of MySQL doesn't support the IN statement, There must be a way around it. If I have to restructure the data I will.

Please help, it's frying my brain!

    Hi,

    Thanks for the tip. But I still can't get it to work. Your suggestion works fine if I want to find a property that is booked but I need to find properties that are not booked for the dates given. What I need is effectively what the database isn't giving me, is there a way to tell it to return the opposite of the result?

    I've tried using the NOT operator in a few different ways. e.g. like this...

    SELECT DISTINCT property.prop_id, property.name FROM property LEFT JOIN bookings ON property.prop_id=bookings.prop_id WHERE ('2005-01-30' > date_from AND '2005-01-30' < date_to)

    But that doesn't return the results I need.

    I'm starting to think this can't be done 🙁

      It looks funny, but this should work.

      SELECT DISTINCT property.prop_id, property.name FROM property LEFT JOIN bookings ON property.prop_id=bookings.prop_id WHERE ('2005-01-30' > date_from AND '2005-01-30' < date_to) 

AND bookings.prop_id is null

        I usually just bite the bullet and use two queries. Slower but easier on the hair. And much easier to maintain.

          tomhath,

          Your suggestion didn't return any results.?!?

          Ahundiak,
          you're probably right.

            Sorry, the query I posted is wrong.

            Try this:

            SELECT DISTINCT property.prop_id, property.name 
FROM property 
LEFT JOIN bookings 
ON '2005-01-30' > date_from 
AND '2005-01-30' < date_to
AND property.prop_id=bookings.prop_id 
WHERE bookings.prop_id is null

              hi there,

              perhaps you can use this function I wrote.
              if you want to do a subselect for each IN(...) without a code explosion, use it like this:

              $sql = "... where id in(".subselect("select id_blah from ...").") and ...";

              you just have to replace the "db_query" part with your own data retrieval code. my db_query function does a query and returns a 2-dim array containing the results (1st dimension: records, 2nd dimension: fields).

              if nothing is returned by the database, the function returns "NULL".
              "select ... where id in(NULL)" will work.

              yours
              matto

              function subselect($sql)
{
        $items_a = db_query($sql);

    if(count($items_a) != 0)
    {
            $items_a1 = array();

            for($i=0; $i<count($items_a); $i++)
            $items_a1[] = $items_a[$i][0];

            $list = implode(",", $items_a1);
    }
    else
    {
            $list = 'NULL';
    }

    return $list;
}
                Write a Reply...