Booking System MySQL

[coxdabd]

Hi all, I've just launched a website where people can hire out equipment. Users can check online whether an item is available for their particular dates. However, I have a problem where things are becoming double booked. I'm checking for existing orders by using the following:

SELECT * FROM hire_order AS o JOIN hire_order_products AS op ON o.order_id = op.order_id WHERE product_id = 2 AND ((post_date BETWEEN ('2012-08-10') AND ('2012-08-14')) OR (home_date BETWEEN ('2012-08-10') AND ('2012-08-14'))) AND order_returned < 2

However, this isn't picking up orders that have been delivered before this and are still out on hire. Any ideas or help would be appreciated

[K. Wolfe]

Could you supply some sample data that might have been excluded from the record set that you would want returned?

[r937]

i was unable to make any sense of your query untill i re-formatted it --

SELECT * 
  FROM hire_order AS o 
  JOIN hire_order_products AS op 
    ON o.order_id = op.order_id 
 WHERE product_id = 2 
   AND (
       ( post_date BETWEEN ('2012-08-10') AND ('2012-08-14') ) 
    OR ( home_date BETWEEN ('2012-08-10') AND ('2012-08-14') )
       ) 
   AND order_returned < 2

you say this isn't picking up orders that have been delivered before this and are still out on hire

unfortunately i can't see how "delivered before this" and "out on hire" are supposed to be implemented here

[cpradio]

Along with the data K. Wolfe suggested, my curiosity wants to know how you established the dates in your query. What if the equipment was hired out on 2012-08-09? My initial thought is your schema might be flawed, or your query is flawed, so I'd also like to see the schemas for the hire_order and the hire_order_products tables so I could assist you in correcting it.

I also agree with r937 that I don't see how your query is doing what you expect it to do (primarily because I think the schema or the query is flawed).