[RESOLVED] SELECT query with LIKE OR and AND not working as expected

cichlid

Hi All,
Apologies for asking what appears to be a simple question. I'm a little confused as to why my below query is not working correctly.

I am trying to search my CMS for the presence of the keyword "bmw" in any of the page titles (page1_title to page10_title). I would like to limit the search only articles with published set to "Yes" and restrict my search only to articles listed in section "1".

The query retrieves results, but they are not limited only to section "1". Is there a rule for the order in which AND, LIKE and OR should be used within a query?

SELECT * FROM cms WHERE published = 'Yes' AND Manufacturer LIKE '%bmw%' OR Range LIKE '%bmw%' OR page1_title LIKE '%bmw%' OR page2_title LIKE '%bmw%' OR page3_title LIKE '%bmw%' OR page4_title LIKE '%bmw%' OR page5_title LIKE '%bmw%' OR page6_title LIKE '%bmw%' OR page7_title LIKE '%bmw%' OR page8_title LIKE '%bmw%' OR page9_title LIKE '%bmw%' OR page10_title LIKE '%bmw%' AND section ='1' ORDER BY sid DESC

Any light anyone can shed upon this would be most gratefully received.

Many thanks!

bogu

U should have your condition like this WHERE cond1 AND (cond2) AND cond3, where cond2 could be (cond2_1 OR cond2_2 OR ...), so your query need to be like this:

SELECT * FROM cms 
WHERE 
	published = 'Yes' AND 
	(
		Manufacturer LIKE '%bmw%' OR 
		Range LIKE '%bmw%' OR 
		page1_title LIKE '%bmw%' OR 
		page2_title LIKE '%bmw%' OR 
		page3_title LIKE '%bmw%' OR 
		page4_title LIKE '%bmw%' OR 
		page5_title LIKE '%bmw%' OR 
		page6_title LIKE '%bmw%' OR 
		page7_title LIKE '%bmw%' OR 
		page8_title LIKE '%bmw%' OR 
		page9_title LIKE '%bmw%' OR 
		page10_title LIKE '%bmw%'
	) AND 
	section ='1' 
ORDER BY sid DESC

so your results need to be published = 'Yes' and any of that fields contains 'bmw' and section = 1

cichlid

Bogu, that's fantastic! Thank you very much indeed for your help. I've learnt a lot from that.

Cheers!

bogu

Np, I'm glad that I could help ...

GL and HF coding ... 😃