1

The following query is not functioning properly.

I am attempting to look at two tables and wish to return all of the propertyID's that do not have a booking associated with the arrival and departure dates of a preexisting booking.

I feel like I should be using a JOIN of some sort (tried referencing this StackOverflow post) but I am not sure that's what I need to utilize ....

In researching I have been attempting to do it this way (this is not working accurately however). Can anyone recommend or share a better method of performing this query to get my desired result?

$btQuery = "
     SELECT * FROM properties 
     WHERE pID NOT IN (
          SELECT pID 
          from property_booking 
          WHERE pbDateArrive BETWEEN CAST('".$_GET['aaDate']."' AS DATE) 
                                 AND CAST('".$_GET['adDate']."' AS DATE) 
          OR    pbDateDepart BETWEEN CAST('".$_GET['aaDate']."' AS DATE) 
                                 AND CAST('".$_GET['adDate']."' AS DATE))";

Greatly appreciate any assistance.

In reply to check the how GET params are being CAST: here is the echo back:

SELECT * 
FROM properties 
WHERE pID NOT IN (
    SELECT pID 
    from property_booking 
    WHERE pbDateArrive BETWEEN CAST('2016-02-05' AS DATE) 
                           AND CAST('2016-02-12' AS DATE) 
    OR    pbDateDepart BETWEEN CAST('2016-02-05' AS DATE) 
                           AND CAST('2016-02-12' AS DATE))
Community
  • 1
  • 1
bones
  • 81
  • 10
  • My suggestion is try to see how mysql casts your GET param with code below and after that we can think more deep why your query doesn't work properlly SELECT CAST('".$_GET['aaDate']."' AS DATE) as date – Volodymyr Sitdikov Feb 05 '16 at 21:10
  • Thank you very much. I have edited the post above to reflect the echo'd back query to show how the GET params for the dates are being cast – bones Feb 05 '16 at 21:17
  • Your query should work as expected. But what do you mean by saying "is not working accurately"? – Volodymyr Sitdikov Feb 05 '16 at 21:34
  • By not accurately I mean: In my booking table I have a property #186 that has a booking from 2016-02-01 to 2016-02-11 ... then when I attempt to check availabilities from lets say 2016-02-05 to 2016-02-17 ... this property #186 is showing up in the list as available when it really isnt. This happens with multiple properties that are already booked, it should only list properties that are not booked during the (GET) dates. – bones Feb 05 '16 at 21:36
  • Here is the actual table data in the booking table:pbID pID pbFirstN pbLastN pbEmail pbDateArrive pbDateDepart 358 186 Test Testn test@somwhere.com 2016-02-01 2016-05-01 – bones Feb 05 '16 at 21:42
  • And here is the property table data pID pName 186 Test Property – bones Feb 05 '16 at 21:43
  • This query is works absollutelly fine, soo you must search at another place, what makes it behave like you described. I am suspicios that you keep your data of booking days not in `DATE` type but in `STRING`. That is just suspicion. Anyway execute this subquery SELECT pID from property_booking WHERE pbDateArrive BETWEEN CAST('2016-02-05' AS DATE) AND CAST('2016-02-12' AS DATE) OR pbDateDepart BETWEEN CAST('2016-02-05' AS DATE) AND CAST('2016-02-12' AS DATE) and look if it will return you the value you don't want to see – Volodymyr Sitdikov Feb 05 '16 at 21:46
  • I will check it out and confirm DATE not STRING as type and let you know,thank you very much for taking the time to offer assistance! ;) – bones Feb 05 '16 at 21:54
  • Please add the test data in the question itself, and add the output you expect to have. – trincot Feb 05 '16 at 22:22

0 Answers0