MYSQL WITH LIKE、OR、AND给出所有结果 - MYSQL WITH LIKE, OR, AND, gives all results

MYSQL WITH LIKE, OR, AND, gives all results

本文关键字：结果 AND WITH LIKE OR MYSQL | 更新日期: 2023-09-27

我正在努力让这个工作。

这是我的代码：

     SELECT a1.*
       , t2.`name` AS project_name
       , t2.`id` AS project_id   
       , u2.`fname` AS fname
    , u2.`lname` AS lname
    , u2.`color` AS color 
    , u2.`id` AS user_id 
    , u3.`fname` AS creatorfname
    , u3.`lname` AS creatorlname
    FROM `activities` AS a1 
    LEFT JOIN `projects` AS t2 ON a1.`projectid`=t2.`id`
    LEFT JOIN `users` AS u2 ON a1.`userid`=u2.`id` 
    LEFT JOIN `users` AS u3 ON a1.`creatorid`=u3.`id` 
    LEFT JOIN `organisations` AS o1 ON u2.`orgid`=o1.`id` WHERE   
          o1.`id`=:orgid AND a1.`active`=1  OR a1.`postarea` LIKE '%newyork%' 
             OR a1.`streetname` LIKE '%newyork%' 
             OR a1.`companyname` LIKE '%newyork%' 
             OR a1.`postarea` LIKE '%newyork%' 
             OR a1.`orgnumber` LIKE '%newyork%' 
             OR a1.`postcode` LIKE '%newyork%' 
             OR a1.`homepage` LIKE '%newyork%' 
             OR a1.`phone1` LIKE '%newyork%' 
             OR a1.`phone2` LIKE '%newyork%' 
             OR a1.`phoneswitch` LIKE '%newyork%' 
             OR a1.`status` LIKE '%newyork%' 
             OR a1.`turnovermin` LIKE '%newyork%' 
             OR a1.`turnovermax` LIKE '%newyork%' 
             OR a1.`description` LIKE '%newyork%' 
             OR a1.`comment` LIKE '%newyork%'
             OR a1.`email` LIKE '%newyork%' AND a1.`projectid`=:projectid     
            AND a1.`userid`=:userid AND a1.`date`>=:datefrom AND 
           a1.`date`<=:dateto ORDER BY a1.`date` DESC

问题是，我得到的每个结果都基于OR LIKE语句。我使用PDO类，因此它是例如：orgid。它应该考虑所有的陈述和标准。我试过（），而且。。。等等，但没有任何结果。

非常感谢您的帮助。

您需要了解逻辑括号。

a AND b OR c OR d表示逻辑污染。括号告诉处理器首先执行哪些操作，而不是对每个操作进行同等排序：

a AND (b OR c)-如果A为真且B或C也是真，则为真

(a AND b) OR c-如果A和B为真，则为真，或者，不管它们如何，如果C为真，而不是

在ORs前后使用()，AND运算符具有更高的优先级，因此在OR之前应用。

 SELECT a1.*
   , t2.`name` AS project_name
   , t2.`id` AS project_id   
   , u2.`fname` AS fname
, u2.`lname` AS lname
, u2.`color` AS color 
, u2.`id` AS user_id 
, u3.`fname` AS creatorfname
, u3.`lname` AS creatorlname
FROM `activities` AS a1 
LEFT JOIN `projects` AS t2 ON a1.`projectid`=t2.`id`
LEFT JOIN `users` AS u2 ON a1.`userid`=u2.`id` 
LEFT JOIN `users` AS u3 ON a1.`creatorid`=u3.`id` 
LEFT JOIN `organisations` AS o1 ON u2.`orgid`=o1.`id`
WHERE   
      o1.`id`=:orgid AND a1.`active`=1  AND
        (   a1.`postarea` LIKE '%newyork%' 
         OR a1.`streetname` LIKE '%newyork%' 
         OR a1.`companyname` LIKE '%newyork%' 
         OR a1.`postarea` LIKE '%newyork%' 
         OR a1.`orgnumber` LIKE '%newyork%' 
         OR a1.`postcode` LIKE '%newyork%' 
         OR a1.`homepage` LIKE '%newyork%' 
         OR a1.`phone1` LIKE '%newyork%' 
         OR a1.`phone2` LIKE '%newyork%' 
         OR a1.`phoneswitch` LIKE '%newyork%' 
         OR a1.`status` LIKE '%newyork%' 
         OR a1.`turnovermin` LIKE '%newyork%' 
         OR a1.`turnovermax` LIKE '%newyork%' 
         OR a1.`description` LIKE '%newyork%' 
         OR a1.`comment` LIKE '%newyork%'
         OR a1.`email` LIKE '%newyork%'
       )
        AND a1.`projectid`=:projectid     
        AND a1.`userid`=:userid AND a1.`date`>=:datefrom AND 
       a1.`date`<=:dateto ORDER BY a1.`date` DESC