MySQL在PHP搜索查询中返回错误的结果

Here is my MySQL query which ignores category field condition, the result also returns results from other category which are not mentioned in the query, here is my mysql query statement

这是我的MySQL查询忽略了类别字段条件,结果还返回了查询中未提及的其他类别的结果,这里是我的mysql查询语句

select *,classifieds.id as id 
from classifieds,classifieds_meta 
where 
    category=1 
    AND meta_key='vehicle_make' 
    and meta_value=2 
    OR meta_key='vehicle_make' 
    and meta_value=3 
    AND (meta_key='vehicle_mileage' and meta_value=9) 
    OR (meta_key='vehicle_mileage' and meta_value=10) 
    OR (meta_key='vehicle_mileage' and meta_value=11) 
    AND classifieds.id=classifieds_meta.classifieds_id 
GROUP BY classifieds.id

the above sql statement returns result from category 1 and 6 instead just 1, can someone please help on this?

上面的sql语句返回类别1和6的结果而不是1,有人可以帮忙吗?

3 个解决方案

#1

Try to add parentheses in the WHERE clause. AND has higher precedence than OR (visit http://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html):

尝试在WHERE子句中添加括号。 AND的优先级高于OR(访问http://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html):

select *,classifieds.id as id 
from classifieds,classifieds_meta 
where
    category=1 AND (
        meta_key='vehicle_make' 
        and meta_value=2 
        OR meta_key='vehicle_make' 
        and meta_value=3 
        AND (meta_key='vehicle_mileage' and meta_value=9) 
        OR (meta_key='vehicle_mileage' and meta_value=10) 
        OR (meta_key='vehicle_mileage' and meta_value=11) 
        AND classifieds.id=classifieds_meta.classifieds_id
    )
GROUP BY classifieds.id

If this is in the WHERE clause for example

例如,如果这是在WHERE子句中

WHERE row1=1 AND row2=2 OR row3=3

--equal to
WHERE (row1=1 AND row2=2) OR row3=3

When row3 equals 3 then row1 could be anything - this is your case.

当row3等于3时,row1可以是任何东西 - 这是你的情况。

#2

You have 4 groups of conditions connected by OR operator and only first group has 'category=1' condition. You need more brackets if you want get ONLY from first category. Like

您有4组条件由OR运算符连接,只有第一组具有'category = 1'条件。如果您想从第一类中获取,则需要更多括号。喜欢

where category=1 AND (rest of conditions)

#3

There's an order of precedence in the AND and OR boolean operations.

AND和OR布尔运算中有一个优先顺序。

Consider:

false AND ( false OR true )   => false

vs.

( false AND false ) OR true   => true

MySQL is not indeterminate when you specify

指定时,MySQL并不是不确定的

false AND false OR true

MySQL has a specific order of precedence, we are guaranteed whether the AND or the OR will be evaluated first.

MySQL具有特定的优先顺序,我们保证首先评估AND或OR。

We can use parens to specify the order that MySQL should evaluate the operations.

我们可以使用parens来指定MySQL应该评估操作的顺序。

#1