mysql query not filtering AND as expected

Question

I have the query listed below, it is out from a PDO statement. Everything in the query works fine, apart from the collectionId = 3 part. Its returning results with other intergers...

I've stared at this for a while and can't spot what is wrong, it all looks fine to me?

SELECT  `Hat`.`id` AS  `Hat_id` ,  `Hat`.`hatCode` AS  `Hat_hatCode` ,  `Hat`.`hatCodeOther` AS  `Hat_hatCodeOther` ,  `Hat`.`name` AS  `Hat_name` ,  `Hat`.`description` AS `Hat_description` ,  `Hat`.`colorId` AS  `Hat_colorId` ,  `Hat`.`collectionId` AS  `Hat_collectionId` ,  `Hat`.`mainPicture` AS  `Hat_mainPicture` ,  `Hat`.`subPicture` AS `Hat_subPicture` ,  `Hat`.`type` AS  `Hat_type` ,  `Hat`.`featured` AS  `Hat_featured` ,  `Hat`.`published` AS  `Hat_published` ,  `Hat`.`deleted` AS  `Hat_deleted` 
FROM  `modx_hats` AS  `Hat` 
WHERE (
`Hat`.`published` =1
AND  `Hat`.`collectionId` =  '3'
AND  `Hat`.`colorId` LIKE  '%||2||%'
OR  `Hat`.`colorId` LIKE  '2||%'
OR  `Hat`.`colorId` LIKE  '%||2'
OR  `Hat`.`colorId` LIKE  '2'
)
LIMIT 0 , 30

Check out SQL operator precendence and this question: http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or — vbo, Jan 14 '14 at 16:08
possible duplicate of [SQL Query multiple AND and OR's not working](http://stackoverflow.com/questions/21096134/sql-query-multiple-and-and-ors-not-working) — codeling, Jan 14 '14 at 16:12

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

Can you try this, Added () for OR grouping

WHERE (
`Hat`.`published` =1
AND  `Hat`.`collectionId` =  '3'
AND ( `Hat`.`colorId` LIKE  '%||2||%'
OR  `Hat`.`colorId` LIKE  '2||%'
OR  `Hat`.`colorId` LIKE  '%||2'
OR  `Hat`.`colorId` LIKE  '2'
)
)

Ref: SQL Query multiple AND and OR's not working

edited May 23 '17 at 12:03

Community

1
1

answered Jan 14 '14 at 16:07

Krish R

22,583
7
50
59

score 1 · Accepted Answer · answered Jan 14 '14 at 16:08

Your OR conditions need to be in parentheses:

SELECT  `Hat`.`id` AS  `Hat_id` ,  `Hat`.`hatCode` AS  `Hat_hatCode` ,  `Hat`.`hatCodeOther` AS  `Hat_hatCodeOther` ,  `Hat`.`name` AS  `Hat_name` ,  `Hat`.`description` AS `Hat_description` ,  `Hat`.`colorId` AS  `Hat_colorId` ,  `Hat`.`collectionId` AS  `Hat_collectionId` ,  `Hat`.`mainPicture` AS  `Hat_mainPicture` ,  `Hat`.`subPicture` AS `Hat_subPicture` ,  `Hat`.`type` AS  `Hat_type` ,  `Hat`.`featured` AS  `Hat_featured` ,  `Hat`.`published` AS  `Hat_published` ,  `Hat`.`deleted` AS  `Hat_deleted` 
FROM  `modx_hats` AS  `Hat` 
WHERE (
`Hat`.`published` =1
AND  `Hat`.`collectionId` =  '3'
AND  (`Hat`.`colorId` LIKE  '%||2||%'
OR  `Hat`.`colorId` LIKE  '2||%'
OR  `Hat`.`colorId` LIKE  '%||2'
OR  `Hat`.`colorId` LIKE  '2')
)
LIMIT 0 , 30

This was it - thanks, as this was the first correct answer I will accept this when I'm allowed to. Will upvote all others though :) — Graeme Leighfield, Jan 14 '14 at 16:17

score 0 · Answer 3 · answered Jan 14 '14 at 16:09

You don't specify what exactly you would like to filter, but your problem most likely is that the conditions get evaluated differently from what you think - How do you expect the OR and AND (which have no parentheses to group them) are evaluated?

As it stands, the query returns all records which fulfill EITHER

`Hat`.`published` =1 AND
`Hat`.`collectionId` =  '3' AND
`Hat`.`colorId` LIKE  '%||2||%' `

OR which fulfill any of those other restrictions:

OR  `Hat`.`colorId` LIKE  '2||%'
OR  `Hat`.`colorId` LIKE  '%||2'
OR  `Hat`.`colorId` LIKE  '2'

It's all a matter of setting the parentheses right. My guess is that you want all records which have published=1, collectionId=3, and one of the colorIds you give. For this, you'd have to group it accordingly:

WHERE (
`Hat`.`published` =1
AND  `Hat`.`collectionId` =  '3'
AND
  (
    `Hat`.`colorId` LIKE  '%||2||%'
    OR  `Hat`.`colorId` LIKE  '2||%'
    OR  `Hat`.`colorId` LIKE  '%||2'
    OR  `Hat`.`colorId` LIKE  '2'
  )
)

mysql query not filtering AND as expected

3 Answers3