CODEWITHSUNDEEP
phpmysql
user1158040
user1158040

Reputation: 47

MYSQL WITH LIKE, OR, AND, gives all results

i'm pulling my hair here trying to get this one to work.

This is my code:

     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

The problem is, I get every result based on the OR LIKE statements. I use PDO class therefore its for example :orgid. It should consider all statements and criterias. I have tried ( ) around AND...and so on but without any results.

Would appreciate some help.

Upvotes: 0

Views: 36

Answers (2)

Mitya
Mitya

Reputation: 34556

You need to learn about logical parentheses.

a AND b OR c OR d means logic contamination. Parentheses tell a processor which operations to carry out first, rather than ranking each equally:

a AND (b OR c) - true if A is true and B or C are also true

(a AND b) OR c - true if A and B are true, or, regardless of them, if C is true instead

Upvotes: 2

Vasfed
Vasfed

Reputation: 18464

Use () around ORs, AND operator has higher priority thus is applied before 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

Upvotes: 1

Related Questions