Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation query not returning the expected results

    here is my query
    Code:
    SELECT *
    FROM tbl_accommodation AS a
    LEFT JOIN tbl_accommodation_types AS at ON at.accommodationID = a.accommodationID
    LEFT JOIN tbl_type AS t ON t.typeID = at.typeID
    LEFT JOIN tbl_accommodation_location AS loc ON loc.accommodationID = a.accommodationID
    LEFT JOIN tbl_locations AS l ON l.locationID = loc.locationID
    WHERE (
    DEGREES( ACOS( SIN( RADIANS( 50.07000 ) ) * SIN( RADIANS( l.latitude ) ) + COS( RADIANS( 50.07000 ) ) * COS( RADIANS( l.latitude ) ) * COS( RADIANS( - 5.69500 - l.longitude ) ) ) ) *60 * 1.1515
    ) <=10
    AND t.typeID =1
    OR t.typeID =2
    OR t.typeID =3
    OR t.typeID =4
    OR t.typeID =5
    AND a.petFriendly =1
    AND a.parking =1
    AND a.smokingPermitted =1
    AND a.wheelchairAdapted =1
    AND a.wifi =1
    AND a.swimmingPool =1
    AND a.airCon =1
    AND a.woodBurner =1
    AND a.centralHeating =1
    AND a.hottub =1
    AND a.garden =1
    AND a.bbq =1
    AND a.cot =1
    AND a.highchair =1
    AND a.pricePN >=0
    AND a.pricePN <=110
    ORDER BY createdDate DESC
    LIMIT 0 , 10
    the problem is its returning records where not all of these
    Code:
    AND a.petFriendly =1
    AND a.parking =1
    AND a.smokingPermitted =1
    AND a.wheelchairAdapted =1
    AND a.wifi =1
    AND a.swimmingPool =1
    AND a.airCon =1
    AND a.woodBurner =1
    AND a.centralHeating =1
    AND a.hottub =1
    AND a.garden =1
    AND a.bbq =1
    AND a.cot =1
    AND a.highchair =1
    are actually 1. can someone please explain what i am doing wrong?

    thanks
    Luke

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,043
    Thanks
    2
    Thanked 316 Times in 308 Posts
    The query returns any rows with a TRUE value for the WHERE clause. Because you are OR'ing values, you are getting all the rows with t.typeID =2, 3, 4, or 5 regardless of the other conditions. You need to put () around the OR terms to make them one expression.

    Also, it is cleaner and easier to build a multi-value OR term using the IN() comparison -

    t.typeID IN(1,2,3,4,5)

    Which actually would eliminate the need to put () around the OR terms because it is already one expression.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    LJackson (12-24-2013)

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,097
    Thanks
    297
    Thanked 12 Times in 12 Posts
    Thanks Mate!!!


  •  

    Posting Permissions

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