CODEWITHSUNDEEP
mysqlsql
Graeme Leighfield
Graeme Leighfield

Reputation: 3005

mysql query not filtering AND as expected

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

Upvotes: 0

Views: 40

Answers (3)

codeling
codeling

Reputation: 11408

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'
  )
)

Upvotes: 0

Digital Chris
Digital Chris

Reputation: 6202

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

Upvotes: 1

Krish R
Krish R

Reputation: 22721

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

Upvotes: 1

Related Questions