Help: MySQL WHERE OR AND

Discussion in 'Programming/Scripts' started by edge, Dec 23, 2009.

  1. edge

    edge Active Member Moderator HowtoForge Supporter

    Some one here who can explain why this query is working fine:

    Code:
    
    $findThis = "testing";
    $inCategory = "cat1";
    
    SELECT * 
    FROM 				products 
    WHERE				products.Desc LIKE '%$findThis%'
    AND 				category= '$inCategory";
    
    It returns the correct info, from cat1.

    But when I want to search in more product fields (in this case also the Title), and when I set the inCategory to an other value it still shows the info from cat1

    Code:
    $findThis = "testing";
    $inCategory = "cat2";
    
    SELECT * 
    FROM 				products 
    WHERE				products.Desc LIKE '%$findThis%'
    OR				products.Title LIKE '%$findThis%'
    AND 				category= '$inCategory";
    
    I have also tested this:

    Code:
    SELECT * 
    FROM 				products 
    WHERE				products.Desc LIKE '%$findThis%' || products.Title LIKE '%$findThis%'
    AND 				category= '$inCategory";
    
    But I get the same results as the 2nd query.

    For some reason the AND does not work correct!

    Anyone here who can show me what the correct syntax is?
     
  2. edge

    edge Active Member Moderator HowtoForge Supporter

    As usual I always find the answer after posting it here (I've been at this for 2 days now)

    The fix is that I need to use brackets () in the WHERE

    Code:
    $findThis = "testing";
    $inCategory = "cat2";
    
    SELECT * 
    FROM 				products 
    WHERE				(products.Desc LIKE '%$findThis%' products.Title LIKE '%$findThis%')
    AND 				category= '$inCategory";
    
     
  3. Ben

    Ben New Member Moderator HowtoForge Supporter ISPConfig Developer

    Sorry that I have not seen this post earlier :)

    Anyhow your former problem was based on the operator precedence: http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html

    the same with 1+2*3 != (1+2)*3 -> 7 vs. 9

    And don't forget about escaping and verifying the userinput before performing the queries (and normally the DB extracted input for further DB related operations as well)
     

Share This Page