CODEWITHSUNDEEP
mysqlwhere-clause
Jay Bhatt
Jay Bhatt

Reputation: 5661

Mysql Query ignoring and where clause

I have a simple mysql query as shown below.

SELECT  `om_user`.`id` ,  `om_user`.`username` ,  `om_user`.`firstName` ,  `om_user`.`lastName` ,  `om_user`.`role` ,  `om_user`.`email` ,  `om_user`.`status` 
FROM  `om_user` 
WHERE (
id LIKE  'admin%'
)
OR (
username LIKE  'admin%'
)
OR (

PASSWORD LIKE  'admin%'
)
OR (
salt LIKE  'admin%'
)
OR (
role LIKE  'admin%'
)
OR (
firstName LIKE  'admin%'
)
OR (
lastName LIKE  'admin%'
)
OR (
addressLine1 LIKE  'admin%'
)
OR (
addressLine2 LIKE  'admin%'
)
OR (
addressLine3 LIKE  'admin%'
)
OR (
city LIKE  'admin%'
)
OR (
county LIKE  'admin%'
)
OR (
postcode LIKE  'admin%'
)
OR (
country LIKE  'admin%'
)
OR (
email LIKE  'admin%'
)
OR (
contactNo LIKE  'admin%'
)
OR (
avatar LIKE  'admin%'
)
OR (

STATUS LIKE  'admin%'
)
OR (
passwordRequestStatus LIKE  'admin%'
)
OR (
passwordResetCount LIKE  'admin%'
)
OR (
passwordResetSalt LIKE  'admin%'
)
OR (
passwordResetTime LIKE  'admin%'
)
OR (
createdOn LIKE  'admin%'
)
OR (
createdBy LIKE  'admin%'
)
OR (
lastUpdateOn LIKE  'admin%'
)
OR (
lastUpdateBy LIKE  'admin%'
)
OR (
active LIKE  'admin%'
)
AND (
active =1
)
ORDER BY  `id` DESC 
LIMIT 0 , 30

But for some reason its ignoring the active=1 condition. Instead of ignoring records who have the value 0 for active column, it still displays them.

Can anyone please help me with this?

Thanks in advance.

Upvotes: 0

Views: 59

Answers (1)

JNevill
JNevill

Reputation: 50273

Rewritten to put all of your OR's in a single paranthetical statement, and to remove the superflous parantheses around every OR clause: 

SELECT 
    `om_user`.`id`,
    `om_user`.`username`,
    `om_user`.`firstName`,
    `om_user`.`lastName`,
    `om_user`.`role`,
    `om_user`.`email`,
    `om_user`.`status`
FROM `om_user`
WHERE 
    (
        id LIKE 'admin%'
        OR username LIKE 'admin%'
        OR PASSWORD LIKE 'admin%'
        OR salt LIKE 'admin%'
        OR ROLE LIKE 'admin%'
        OR firstName LIKE 'admin%'
        OR lastName LIKE 'admin%'
        OR addressLine1 LIKE 'admin%'
        OR addressLine2 LIKE 'admin%'
        OR addressLine3 LIKE 'admin%'
        OR city LIKE 'admin%'
        OR county LIKE 'admin%'
        OR postcode LIKE 'admin%'
        OR country LIKE 'admin%'
        OR email LIKE 'admin%'
        OR contactNo LIKE 'admin%'
        OR avatar LIKE 'admin%'
        OR STATUS LIKE 'admin%'
        OR passwordRequestStatus LIKE 'admin%'
        OR passwordResetCount LIKE 'admin%'
        OR passwordResetSalt LIKE 'admin%'
        OR passwordResetTime LIKE 'admin%'
        OR createdOn LIKE 'admin%'
        OR createdBy LIKE 'admin%'
        OR lastUpdateOn LIKE 'admin%'
        OR lastUpdateBy LIKE 'admin%'
        OR active LIKE 'admin%'
    )
    AND active = 1
ORDER BY 
    `id` DESC LIMIT 0,
    30

This will test all of the OR conditions. If any one of them are TRUE AND active = 1 then it will return the record.

Also should mention that OR active like 'admin%' is superflous. It and active=1 can't both be true at the same time.

Upvotes: 1

Related Questions