Page 2 of 2 First 12
  • Jump to page:
    #16
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Right guys,I have tidied up the code

    [mysql]SELECT *
    FROM rugs AS r
    LEFT JOIN rug_colour AS rcol ON rcol.rugid = r.rugid
    LEFT JOIN rug_country as rcoun ON rcoun.rugid = r.rugid
    LEFT JOIN rug_type as rtype ON rtype.rugid = r.rugid
    WHERE rcol.colour IN ('$black','$brown','$white','$darkblue','$denimblue','$green','$grey','$oatmeal','$paleblue','$purpl e','$red','$rust','$brickorange') OR rcoun.rugcountry IN ('$persian','$turk','$afghan', '$pakis','$kash','$india','$nepal','$china') OR rtype.rugtype IN ('$city','$village','$tribal','$geo','$floral','$plain','$bordered','$funky') AND (price BETWEEN $pricemin AND $pricemax) AND (width BETWEEN $widthmin AND $widthmax) AND (length BETWEEN $lengthmin AND $lengthmax)[/mysql]

    Everything works up until the AND statements at the end, the results completely ignore the AND statements. If I change them to OR's, they are usable. I have tried using parantheses, swapping the statements around. From what ive read, there is n reason the and/or's wont work together.

    Any pointers would be greatly received
  2. #17
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    The specific problem is one of absent parentheses, but you may also want to rethink how your variables work. Also, as presently written, your [OUTER] JOINs will behave as [INNER] ones. You seem to happy with that behaviour so you may as well write them that way to begin with...
    Code:
    SELECT r.*
      FROM rugs r
      JOIN rug_colour rcol 
        ON rcol.rugid = r.rugid
      JOIN rug_country rcoun 
        ON rcoun.rugid = r.rugid
      JOIN rug_type rtype 
        ON rtype.rugid = r.rugid
     WHERE (rcol.colour IN ($colours) 
        OR rcoun.rugcountry IN ($countries) 
        OR rtype.rugtype IN ($types)) 
       AND (price BETWEEN $pricemin AND $pricemax) 
       AND (width BETWEEN $widthmin AND $widthmax) 
       AND (length BETWEEN $lengthmin AND $lengthmax);
    Unless dealing with a vast amount of data, if it was me, I'd just bring back the whole dataset and allow the user to manage filtering client-side using something like isotope - much more visually engaging (although a little tricky to write)
  4. #18
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Originally Posted by cafelatte
    The specific problem is one of absent parentheses, but you may also want to rethink how your variables work. Also, as presently written, your [OUTER] JOINs will behave as [INNER] ones. You seem to happy with that behaviour so you may as well write them that way to begin with...
    Code:
    SELECT r.*
      FROM rugs r
      JOIN rug_colour rcol 
        ON rcol.rugid = r.rugid
      JOIN rug_country rcoun 
        ON rcoun.rugid = r.rugid
      JOIN rug_type rtype 
        ON rtype.rugid = r.rugid
     WHERE (rcol.colour IN ($colours) 
        OR rcoun.rugcountry IN ($countries) 
        OR rtype.rugtype IN ($types)) 
       AND (price BETWEEN $pricemin AND $pricemax) 
       AND (width BETWEEN $widthmin AND $widthmax) 
       AND (length BETWEEN $lengthmin AND $lengthmax);
    Unless dealing with a vast amount of data, if it was me, I'd just bring back the whole dataset and allow the user to manage filtering client-side using something like isotope - much more visually engaging (although a little tricky to write)

    Thanks for the swift response Caffelatte, unfortunately when I adjusted it with the extra parentheses I now get no results.

    Is there a way to use a subselect to find the MUST HAVE criteria... price,size and then apply the CAN HAVE criteria, country,colour? I looked at isotope, but im trying to get my head around SQL stuff (but thanks for that pointer anyway).
  6. #19
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    Originally Posted by mashamit
    Thanks for the swift response Caffelatte, unfortunately when I adjusted it with the extra parentheses I now get no results.
    If you try something new, please post the changed code. That way we can better see where you might have it wrong.

    Originally Posted by mashamit
    Is there a way to use a subselect to find the MUST HAVE criteria... price,size and then apply the CAN HAVE criteria, country,colour? I looked at isotope, but im trying to get my head around SQL stuff (but thanks for that pointer anyway).
    This is based on how you combine the use of AND, OR and parentheses.
    Take a look at the two small example below.
    Example 1:
    sql Code:
     
    WHERE (rcoun.rugcountry = "USA"  AND rtype.rugtype = "Type1") OR rtype.rugtype = "Type2"


    This will return all rugs from USA with Type 1 and all Type2, independent of the country

    Example 2:
    sql Code:
     
    WHERE rcoun.rugcountry = "USA"  AND (rtype.rugtype = "Type1" OR rtype.rugtype = "Type2")


    This will return all rugs from USA which are either Type1 or Type2

    As you can see, the placement of parentheses is crucial in understand of what result you expect, compare if you just write:
    sql Code:
     
    WHERE rcoun.rugcountry = "USA"  AND rtype.rugtype = "Type1" OR rtype.rugtype = "Type2"

    Will the above return the result on example 1 or example 2?

    Basically, for MUST HAVE you use AND, for CAN HAVE you use OR.
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Originally Posted by MrFujin
    If you try something new, please post the changed code. That way we can better see where you might have it wrong.



    This is based on how you combine the use of AND, OR and parentheses.
    Take a look at the two small example below.
    Example 1:
    sql Code:
     
    WHERE (rcoun.rugcountry = "USA"  AND rtype.rugtype = "Type1") OR rtype.rugtype = "Type2"


    This will return all rugs from USA with Type 1 and all Type2, independent of the country

    Example 2:
    sql Code:
     
    WHERE rcoun.rugcountry = "USA"  AND (rtype.rugtype = "Type1" OR rtype.rugtype = "Type2")


    This will return all rugs from USA which are either Type1 or Type2

    As you can see, the placement of parentheses is crucial in understand of what result you expect, compare if you just write:
    sql Code:
     
    WHERE rcoun.rugcountry = "USA"  AND rtype.rugtype = "Type1" OR rtype.rugtype = "Type2"

    Will the above return the result on example 1 or example 2?

    Basically, for MUST HAVE you use AND, for CAN HAVE you use OR.
    Hi MrFujin,

    Sorry I did mean to pop the code in, this is what i have now

    sql Code:
    SELECT *
    FROM rugs AS r
    JOIN rug_colour AS rcol 
    ON rcol.rugid  = r.rugid 
    JOIN rug_country AS rcoun 
    ON rcoun.rugid  = r.rugid
    JOIN rug_type AS rtype
    ON rtype.rugid  = r.rugid 
    WHERE (rcol.colour IN ('$black','$brown','$white','$darkblue','$denimblue','$green','$grey','$oatmeal','$paleblue','$purpl  e','$red','$rust','$brickorange') 
    OR  rcoun.rugcountry IN ('$persian','$turk','$afghan', '$pakis','$kash','$india','$nepal','$china') 
    OR rtype.rugtype IN ('$city','$village','$tribal','$geo','$floral','$plain','$bordered','$funky'))
    AND (price BETWEEN $pricemin AND $pricemax)
    AND (width BETWEEN $widthmin AND $widthmax)
    AND (LENGTH BETWEEN $lengthmin AND $lengthmax);


    Its just ignoring the AND statements
  10. #21
  11. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    What do you mean with "ignoring the AND statement"?

    Do you get the correct/expected result if you run the OR and AND for them self?
    sql Code:
     
    WHERE (rcol.colour IN ('$black','$brown','$white','$darkblue','$denimblue','$green','$grey','$oatmeal','$paleblue','$puple  ','$red','$rust','$brickorange')
    OR  rcoun.rugcountry IN ('$persian','$turk','$afghan', '$pakis','$kash','$india','$nepal','$china')
    OR rtype.rugtype IN ('$city','$village','$tribal','$geo','$floral','$plain','$bordered','$funky'))


    sql Code:
     
    WHERE (price BETWEEN $pricemin AND $pricemax)
    AND (width BETWEEN $widthmin AND $widthmax)
    AND (LENGTH BETWEEN $lengthmin AND $lengthmax);
  12. #22
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Originally Posted by mashamit
    Hi MrFujin,

    Sorry I did mean to pop the code in, this is what i have now

    sql Code:
    SELECT *
    FROM rugs AS r
    JOIN rug_colour AS rcol 
    ON rcol.rugid  = r.rugid 
    JOIN rug_country AS rcoun 
    ON rcoun.rugid  = r.rugid
    JOIN rug_type AS rtype
    ON rtype.rugid  = r.rugid 
    WHERE (rcol.colour IN ('$black','$brown','$white','$darkblue','$denimblue','$green','$grey','$oatmeal','$paleblue','$purpl  e','$red','$rust','$brickorange') 
    OR  rcoun.rugcountry IN ('$persian','$turk','$afghan', '$pakis','$kash','$india','$nepal','$china') 
    OR rtype.rugtype IN ('$city','$village','$tribal','$geo','$floral','$plain','$bordered','$funky'))
    AND (price BETWEEN $pricemin AND $pricemax)
    AND (width BETWEEN $widthmin AND $widthmax)
    AND (LENGTH BETWEEN $lengthmin AND $lengthmax);


    Its just ignoring the AND statements
    It appears that I must select at least one of the optional criteria in order for the query to work. So to allow for this I have used an IF ELSE php statement to check if any of the optional criteria are click, then produce the string depending on the outcome.

    php Code:
    <?
    		if($afghan == 'NULL' &&
    			$black == 'NULL' && 
    				 $bordered == 'NULL' &&
    					 $brickorange == 'NULL' &&
    						 $brown == 'NULL' && 
    							$china == 'NULL' && 
    									$darkblue == 'NULL' && 
    										$denimblue == 'NULL' && 
    											$floral == 'NULL' &&
    												 $funky == 'NULL' && 
    												 	$geo == 'NULL' && 
    														$green == 'NULL' &&
    															 $grey == 'NULL' && 
    															 	$india == 'NULL' && 
    																	$kash == 'NULL' && 
    																		$nepal == 'NULL' && 
    																			$oatmeal == 'NULL' && 
    																				$pakis == 'NULL' && 
    																					$paleblue == 'NULL' &&
    																						 $persian == 'NULL'  && 
    																						 	$plain == 'NULL' && 
    																								$purple == 'NULL' && 
    																									$red == 'NULL' && 
    																										$rust == 'NULL' &&
    																																	 	$turk == 'NULL'  && 
    																														$white == 'NULL') 
    		{
    				$strSQL = "SELECT *
    FROM rugs AS r
    JOIN rug_colour AS rcol 
    ON rcol.rugid  = r.rugid 
    JOIN rug_country AS rcoun 
    ON rcoun.rugid  = r.rugid
    JOIN rug_type AS rtype
    ON rtype.rugid  = r.rugid 
    WHERE  r.price BETWEEN $pricemin AND $pricemax
    AND r.width BETWEEN $widthmin AND $widthmax
    AND r.length BETWEEN $lengthmin AND $lengthmax"; 
    			 }
    			 else
    			 {
    					$strSQL = "SELECT *
    FROM rugs AS r
    JOIN rug_colour AS rcol 
    ON rcol.rugid  = r.rugid 
    JOIN rug_country AS rcoun 
    ON rcoun.rugid  = r.rugid
    JOIN rug_type AS rtype
    ON rtype.rugid  = r.rugid 
    WHERE  rcol.colour IN ('$black','$brown','$white','$darkblue','$denimblue','$green','$grey','$oatmeal','$paleblue','$purpl  e','$red','$rust','$brickorange') OR  rcoun.rugcountry IN ('$persian','$turk','$afghan', '$pakis','$kash','$india','$nepal','$china') OR rtype.rugtype IN ('$city','$village','$tribal','$geo','$floral','$plain','$bordered','$funky') AND r.price BETWEEN $pricemin AND $pricemax
    AND r.width BETWEEN $widthmin AND $widthmax
    AND r.length BETWEEN $lengthmin AND $lengthmax";
    			 }



    This solves the issue, but hasnt answered why the query didnt work so any further help in understanding why would be greatly appreciated.
  14. #23
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    13
    Rep Power
    0
    Originally Posted by MrFujin
    What do you mean with "ignoring the AND statement"?

    Do you get the correct/expected result if you run the OR and AND for them self?
    sql Code:
     
    WHERE (rcol.colour IN ('$black','$brown','$white','$darkblue','$denimblue','$green','$grey','$oatmeal','$paleblue','$puple  ','$red','$rust','$brickorange')
    OR  rcoun.rugcountry IN ('$persian','$turk','$afghan', '$pakis','$kash','$india','$nepal','$china')
    OR rtype.rugtype IN ('$city','$village','$tribal','$geo','$floral','$plain','$bordered','$funky'))


    sql Code:
     
    WHERE (price BETWEEN $pricemin AND $pricemax)
    AND (width BETWEEN $widthmin AND $widthmax)
    AND (LENGTH BETWEEN $lengthmin AND $lengthmax);



    individually they both work, equally if I change the AND's to OR's, the whole query works.... same with OR's to AND's. It seems to be when both are used in the same query the issue appears (except if I select at least 1 optional criteria... see my last post)
  16. #24
  17. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,614
    Rep Power
    1945
    Originally Posted by mashamit
    except if I select at least 1 optional criteria... see my last post)
    You are correct that one of the OR condition should be true, because the condition "block" are connected with AND.

    I saw you check all your variables for NULL using AND.
    How do you assign values to all the variables? Where do the values come from?
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo