Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

MySql select query with multiple LIKE clause not working

Hi,

I have the following MySql statement that is generated by a script based upon the input from the user.
If the user enters more than one term like in this case 'glass plates' I want it to locate all products where the product code or description is LIKE glass OR plates.
This is fine but slightly more complicated by the additional AND clauses which end up with the malformed query below. I have tried a few alternatives but this query result needs to be paginated and they did not seem to work.

SELECT ProdHead.ProdCode, ProdHead.ProdDesc, ProdSku.Colour  
FROM ProdHead
INNER JOIN ProdSku ON ProdHead.ProdCode = ProdSku.ProdCode
INNER JOIN ProdTitleIndex ON ProdHead.ProdCode = ProdTitleIndex.ProdCode
WHERE Concat(ProdHead.ProdDesc,Prodhead.prodcode) LIKE '%glass%'
OR Concat(ProdHead.ProdDesc,Prodhead.prodcode) LIKE '%plates%'
AND ProdHead.Avail = 1
AND ProdSku.Avail = 1
AND ProdHead.SubGroup > 0
AND ProdHead.SubGroup = 228
ORDER BY ProdHead.ProdDesc
LIMIT 0, 10

The only way I can get this to work is by repeating the AND clauses after each OR cluase which is just silly so I was hoping there may be a way of doing something such as

WHERE ........ IN('%glass%','%plates%')


Any help appreciated.

Regards
Leigh
0
Lmillard
Asked:
Lmillard
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>WHERE ........ IN('%glass%','%plates%')

sorry, won't work, unfortunately ...

apart from that: you mix AND an OR, so you need ()
SELECT ProdHead.ProdCode, ProdHead.ProdDesc, ProdSku.Colour  
FROM ProdHead
INNER JOIN ProdSku ON ProdHead.ProdCode = ProdSku.ProdCode
INNER JOIN ProdTitleIndex ON ProdHead.ProdCode = ProdTitleIndex.ProdCode
WHERE ( Concat(ProdHead.ProdDesc,Prodhead.prodcode) LIKE '%glass%'
     OR Concat(ProdHead.ProdDesc,Prodhead.prodcode) LIKE '%plates%'
      )
AND ProdHead.Avail = 1
AND ProdSku.Avail = 1
AND ProdHead.SubGroup > 0
AND ProdHead.SubGroup = 228
ORDER BY ProdHead.ProdDesc
LIMIT 0, 10 

Open in new window

0
 
cyberkiwiCommented:
You need to properly bracket the LIKE's

SELECT ProdHead.ProdCode, ProdHead.ProdDesc, ProdSku.Colour  
FROM ProdHead
INNER JOIN ProdSku ON ProdHead.ProdCode = ProdSku.ProdCode
INNER JOIN ProdTitleIndex ON ProdHead.ProdCode = ProdTitleIndex.ProdCode
WHERE
(     Concat(ProdHead.ProdDesc,Prodhead.prodcode) LIKE '%glass%'
      OR Concat(ProdHead.ProdDesc,Prodhead.prodcode) LIKE '%plates%' )
AND ProdHead.Avail = 1
AND ProdSku.Avail = 1
AND ProdHead.SubGroup > 0
AND ProdHead.SubGroup = 228
ORDER BY ProdHead.ProdDesc
LIMIT 0, 10

The IN form doesn't work, IN only takes exact matches.
Though the query above will work, to perform decently, you may want to try splitting it out and timing each alternative.

SELECT ProdHead.ProdCode, ProdHead.ProdDesc, ProdSku.Colour  
FROM ProdHead
INNER JOIN ProdSku ON ProdHead.ProdCode = ProdSku.ProdCode
INNER JOIN ProdTitleIndex ON ProdHead.ProdCode = ProdTitleIndex.ProdCode
WHERE
(     ProdHead.ProdDesc LIKE '%glass%' OR
      Prodhead.prodcode LIKE '%glass%' OR
      ProdHead.ProdDesc LIKE '%plates%' OR
      Prodhead.prodcode LIKE '%plates%')
AND ProdHead.Avail = 1
AND ProdSku.Avail = 1
AND ProdHead.SubGroup > 0
AND ProdHead.SubGroup = 228
ORDER BY ProdHead.ProdDesc
LIMIT 0, 10
0
 
LmillardAuthor Commented:
Great, thanks very much
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now