Use "or" and "and" correctly to select

Question

I have a problem of connecting "and" and "or" together. Here's my code:

SELECT * FROM table WHERE name LIKE '$name'  
  AND color LIKE '$color' 
  OR color2 LIKE '$color2' 
  OR color3 LIKE '$color3' 
  AND gender  LIKE '$gender'  -- when I add those 3, search stops working.
  OR gender2 LIKE '$gender2' 
  OR gender3 LIKE '$gender3'

The problem is that when I add "gender" to select, my search stops working... Is there a problem with double "or" or maybe with the positioning?

Make proper use of parenthesis. Its like doing algebra, you have to put the parenthesis around stuff that goes together. — developerwjk, Jan 12 '15 at 23:45
The MySQL **`AND`** and **`OR`** operators have an **order of precedence**. That means those operators get evaluated in a specific order. It's likely that this differs from the order you expected. You can explicitly specify the sequence of operations by using parentheses. (The operations within parentheses are evaluated first.) — spencer7593, Jan 12 '15 at 23:52

score 1 · Accepted Answer · edited May 23 '17 at 11:43

Use parentheses to separate your ORs and ANDs into the proper groups. Some additional formatting of the SQL will make it more obvious what your intentions are:

SELECT 
    * 
FROM 
    table 
WHERE 
    name LIKE '$name'  
    AND 
    (
        color LIKE '$color' 
        OR 
        color2 LIKE '$color2' 
        OR 
        color3 LIKE '$color3'
    ) 
    AND 
    (
        gender LIKE '$gender'
        OR 
        gender2 LIKE '$gender2' 
        OR 
        gender3 LIKE '$gender3'
    )

Lastly, you're not using any wildcards in your LIKE expressions. I would suggest simply using = instead, as it will be faster.

I would suggest reading through the following MySQL reference documentation to get a clearer picture on why this works the way it does:

12.3.1 Operator Precedence

Or this Q/A here:

Mysql or/and precedence?

Hi, thanks for answering. I will look into this tomorrow. And if it works, I will tick it as the answer. — Rainbow, Jan 12 '15 at 23:50

score 0 · Answer 2 · answered Jan 12 '15 at 23:49

I'd recommend using parenthesis to make things more obvious.

You should have a look into Operator Precedence (http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html) to see what statement will be evaluated first, second, etc. Parenthesis make this much more explicit.

I was going to post fixed SQL, but Cory has already done it above

Use "or" and "and" correctly to select

2 Answers2