SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2005
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code SQL:
    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?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    When using AND and OR in the WHERE conditions, you'd better use parentheses around the conditions to avoid unwanted results:
    Code:
    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%'
          )

  3. #3
    SitePoint Evangelist
    Join Date
    Oct 2005
    Posts
    405
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •