SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Apr 2006
    Location
    London, United Kingdom
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search with multiple terms

    How do I code a MySQL search query with multiple terms that could be in any arbitratry order?

    For example this is the very simple format I'm currently using

    PHP Code:
    WHERE terms LIKE '%$search_terms%' 
    If I have an item called "Kings Schools Flight Test DVD", if I enter "Kings schools" into the search box, it shows up. But if I type "Kings flight test" it doesn't because of the way the LIKE wildcard works.

    Any idea how to code the search query so that it takes into account all the term but regardless of the order or composition of the terms? I know I can explode the terms into an array and then break it up into multiple OR WHERE conditions but how would I sort it by relevance this way?
    eFlair Design | Premium Design Services and Web Consultation

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by emkay View Post
    ... but how would I sort it by relevance this way?
    Code:
    SELECT othercolumns
         , CASE WHEN terms LIKE '%$search_term1%' 
                THEN 1 ELSE 0 END +
           CASE WHEN terms LIKE '%$search_term2%' 
                THEN 1 ELSE 0 END +
           CASE WHEN terms LIKE '%$search_term3%' 
                THEN 1 ELSE 0 END +
           CASE WHEN terms LIKE '%$search_term4%' 
                THEN 1 ELSE 0 END 
             AS relevancy
      FROM daTable
     WHERE terms LIKE '%$search_term1%'  
        OR terms LIKE '%$search_term2%'  
        OR terms LIKE '%$search_term3%'  
        OR terms LIKE '%$search_term4%'  
    ORDER
        BY relevancy DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Apr 2006
    Location
    London, United Kingdom
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT othercolumns
         , CASE WHEN terms LIKE '%$search_term1%' 
                THEN 1 ELSE 0 END +
           CASE WHEN terms LIKE '%$search_term2%' 
                THEN 1 ELSE 0 END +
           CASE WHEN terms LIKE '%$search_term3%' 
                THEN 1 ELSE 0 END +
           CASE WHEN terms LIKE '%$search_term4%' 
                THEN 1 ELSE 0 END 
             AS relevancy
      FROM daTable
     WHERE terms LIKE '%$search_term1%'  
        OR terms LIKE '%$search_term2%'  
        OR terms LIKE '%$search_term3%'  
        OR terms LIKE '%$search_term4%'  
    ORDER
        BY relevancy DESC
    Thank you very much for the prompt reply, I shall try it out
    eFlair Design | Premium Design Services and Web Consultation


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
  •