SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Booking System MySQL

    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:

    Code MySQL:
    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

  2. #2
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Could you supply some sample data that might have been excluded from the record set that you would want returned?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i was unable to make any sense of your query untill i re-formatted it --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,162
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    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).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •