CODEWITHSUNDEEP
phpmysqlsqlpdo
Graeme Leighfield
Graeme Leighfield

Reputation: 2985

SQL Query multiple AND and OR's not working

I have a cell that contains values separated by a double pipe. I am trying to search over the contents of this cell with the following, (where 10 is the number to search for) %10, 10%, %10% and 10

My query only seems to be returning 10. none of the other variations. Can someone please advise me why it's not working?

Many thanks in advance. (the SQL query you see below is what is exported from a prepared PDO query statement)

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` =  '1'
)
AND  `Hat`.`colorId` =  '%||10||%'
OR  `Hat`.`colorId` =  '10||%'
OR  `Hat`.`colorId` =  '%||10'
OR  `Hat`.`colorId` =  '10'
)
LIMIT 0 , 30

Upvotes: 3

Views: 875

Answers (6)

Raul Andres
Raul Andres

Reputation: 3806

I think that you should use LIKE instead of =:

AND  `Hat`.`colorId` LIKE '%||10||%'
OR  `Hat`.`colorId` LIKE '10||%'
OR  `Hat`.`colorId` LIKE '%||10'
OR  `Hat`.`colorId` =  '10'

Upvotes: 10

Malachi
Malachi

Reputation: 3221

you need to escape the Percentage Symbol

the answer to this question should show you how to do it.

You could also create a query like this

SELECT * FROM HatTable
WHERE colorId LIKE '%10%'

this should give you all the options that have 10 in them.

Update

This will give you anything that has a 10 in it. so this is probably not what you are looking for.

Examples of selection using the above query

  • 3210

  • ||10||

  • ||10

  • 10||

  • 10

  • 1032

  • 435262 10 32456234

Upvotes: 1

saamorim
saamorim

Reputation: 3905

Look at this running sql fiddle based on your schema/data/query: http://sqlfiddle.com/#!2/48da8/3

You should use the LIKE operator and put your parenthesis correctly based on the AND and OR priority. Also, cleaning up your query, it looks like this: 

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 = '1' 
       AND ( Hat.colorid LIKE '%||10||%' 
              OR Hat.colorid LIKE '10||%' 
              OR Hat.colorid LIKE '%||10' 
              OR Hat.colorid LIKE '10' ) 
LIMIT  0, 30

The backquote isn't necessary, as long as you don't use restricted naming characters, like the space character.

Upvotes: 2

Andy Lester
Andy Lester

Reputation: 93676

I believe what you want is this:

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

Note that I removed the backticks from all your table and column definitions except for "type" which is a reserved word.

Upvotes: 1

Awlad Liton
Awlad Liton

Reputation: 9351

For wildcart you should use LIKE
Try this:

 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` =  '1'
    )
    AND  `Hat`.`colorId` LIKE  '%||10||%')
    OR  `Hat`.`colorId` LIKE  '10||%'
    OR  `Hat`.`colorId` LIKE '%||10'
    OR  `Hat`.`colorId` LIKE '10'

    )

LIMIT 0 , 30

Upvotes: 3

Krish R
Krish R

Reputation: 22721

Can you try this, Also, For better results - added () for all OR i.e grouping 

WHERE (
(
`Hat`.`published` =1
AND  `Hat`.`collectionId` =  '1'
)
AND ( `Hat`.`colorId` LIKE '%||10||%'
....^
OR  `Hat`.`colorId`  LIKE  '10||%'
OR  `Hat`.`colorId` LIKE  '%||10'
OR  `Hat`.`colorId` =  '10'
  )
..^
)
LIMIT 0 , 30

Upvotes: 2

Related Questions