RE: SQL Filter Question

I’m trying to do a SQL query where it filters the result set for a search on a website.

The search is meant to filter if its a news post or a faq or all site.

It uses a number to identify which one however the SQL query always returns the same amount of rows.

SELECT DISTINCT searches.search_page_area, searches.search_page_key, searches.search_page_content, searches.search_page_title, searches.search_page_description FROM searches WHERE searches.search_page_area = 2 AND searches.search_page_title LIKE '%water%' OR searches.search_page_description LIKE '%water%' OR searches.search_page_keywords LIKE '%water%' 

Could anyone help?

When using AND and OR in the WHERE conditions, you’d better use parentheses around the conditions to avoid unwanted results:


SELECT DISTINCT 
    searches.search_page_area
  , searches.search_page_key
  , searches.search_page_content
  , searches.search_page_title
  , searches.search_page_description 
FROM searches 
WHERE searches.search_page_area = 2 
AND   (   searches.search_page_title LIKE '%water%' 
       OR searches.search_page_description LIKE '%water%' 
       OR searches.search_page_keywords LIKE '%water%'
      )

Thanks

You should also likely look up how to use FULLTEXT indexing. Any search that begins with a wild card (%) forces a full table scan. You are doing three table scans. With fulltext index you’d be able to search for the matching term across all three fields at once and make use of the fulltext index.