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
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.