SitePoint Sponsor

User Tag List

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

    MySQL Where Not Working Correctly

    Hi all, I seem to be having a couple of issues with a query. For some reason, it fetches random orders instead of those with the product_id = 2. Not to sure why this is happening. This is part of a booking system but is proving very frustrating! Any help would be hugely appreciated

    Code MySQL:
    SELECT * FROM hire_order AS o 
    LEFT JOIN hire_order_products AS op 
    ON o.order_id = op.order_id 
    WHERE product_id = 2 AND 
    post_date BETWEEN ('2012-08-17') AND ('2012-08-23') OR 
    home_date BETWEEN ('2012-08-17') AND ('2012-08-23')
    AND order_returned != 2

    There seems to be a problem with the dates, for when I remove the dates BETWEEN it works, but I need the dates to grab the correct availability.

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Without trying it it's very tricky but do the 'random' results fall between the home_date query? This isn't optimised but start by putting all brackets in place:

    Code MySQL:
    SELECT * FROM hire_order AS o 
    LEFT JOIN hire_order_products AS op 
    ON o.order_id = op.order_id 
    WHERE product_id = 2 AND 
    ((post_date BETWEEN ('2012-08-17') AND ('2012-08-23')) OR 
    (home_date BETWEEN ('2012-08-17') AND ('2012-08-23')))
    AND order_returned != 2

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2010
    Posts
    503
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi RichardAskew, that's absolutely brilliant, it's working like an absolute charm now, thought it may have something to do with brackets. Thank you so much, it's hugely appreciated and a very quick response also. You've made my week, I can now get my new website up!

  4. #4
    SitePoint Zealot
    Join Date
    Jul 2012
    Location
    Scarborough, North Yorkshire, United Kingdom
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem!

    I believe it was previously looking for:

    Code MySQL:
    product_id = 2 AND 
    post_date BETWEEN ('2012-08-17') AND ('2012-08-23')

    OR

    Code MySQL:
    home_date BETWEEN ('2012-08-17') AND ('2012-08-23')
    AND order_returned != 2

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If product_id is a column of the hire_order_products tables, then you can change that LEFT JOIN in an INNER JOIN.
    Or you can move any conditions that use columns from the left joined table to the ON clause, but I don't think you want data from the hire_order table that doesn't have any corresponding rows in the hire_order_products table?


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
  •