SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Non-Member esllou's Avatar
    Join Date
    Apr 2004
    Location
    small tropical isle
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    search multiple words in three fields (was "newbie question concerning mysql query")

    I have three fields in a table "documents"

    - summary
    - description
    - keywords (can be null, this one)

    one entry is, for example

    "Looking After Your Dog", "Everything you need to take care of your dog and ensuring optimum health.","dog vets puppies"

    which query syntax would I need to find this document if someone typed into a search box:

    - "dog health" (two separate words from same field)
    - "health puppies" (two separate words from 2 fields)
    - "taking care dogs" (one word not present anywhere, one word present, one word present but in different form)


    is there a single "silver bullet" sql query I could use for a search script that would find this document in all three scenarios...or is mysql not capable of this type of boolean search functions?

  2. #2
    SitePoint Addict lmasi02's Avatar
    Join Date
    Aug 2004
    Location
    Zambia
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I feel you need to explode(dont know language you are using, explode works in php,) you search using put the resuls in an array. You the search for those word
    Power of Knowledge

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by esllou
    is there a single "silver bullet" sql query I could use for a search script that would find this document in all three scenarios...
    yes, there is, but first you need to tell us how you want ANDs and ORs handled

    let's take one example, "after care" -- this is just like "health puppies"

    what do you want returned?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member esllou's Avatar
    Join Date
    Apr 2004
    Location
    small tropical isle
    Posts
    181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if someone puts "after care", I would want that document to be returned, yes.

    I have found the LIKE %dog% query to be too limiting and as I am just starting out, wasn't sure what query I could make so as to return that doc if people used a combination of search strings that were/weren't present in one or more fields.

    does that answer your question?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sounds like you want all ORs then

    let's say someone enters two words, "health puppies"
    Code:
    select summary
         , description
         , keywords
      from documents
     where (
           summary     like '%health%'
        or description like '%health%'
        or keywords    like '%health%'
           )
        or (
           summary     like '%puppies%'
        or description like '%puppies%'
        or keywords    like '%puppies%'
           )
    repeat this pattern the more words there are
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i personally hate these kind of searches. if i enter "id card holder" on office depot's website, i get 900+ entires because it OR's them all. i want the 5 results where ALL of my words are present.

  7. #7
    Non-Member Gator99's Avatar
    Join Date
    Sep 2004
    Location
    Florida
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The easiest way would be to do a query like this, where $op is either "and" or "or" depending if you want the exact phrase:
    Code:
    $sql="select * from documents where concat(summary, descriptions,keywords) rlike 'health' $op concat(summary, descriptions,keywords) rlike 'puppies'";
    To kind of automate this:
    Code:
    $sql=select * from documents";
    //$string is your filtered and validated search phrase
    $kws=split("/\s+/",$string);
    $cnd=array();
    $op=" and "; //exact search phrase
    $str="concat(summary,descriptions,keywords) rlike '"
    for($xx=0;$xx<count($kws)$xx++){
      array_push($cnd,"$str$cnd[$xx]'");
    }
    $cond=join($op,$cnd);
    $sql.=" where $cond";
    The problem you'd run in with here though is performance. I'm not sure that even if you had the 3 columns indexed, if that would be taken into consideration with the concat function. What you could do is to make a fourth column that would be a combination of the three critea columns. In this example I'll call it stext. Then you'd remove the indexes from the other 3 columns and create a new column stext and index that:
    Code:
    //To populate stext
    $sql ="update document set stext=concat(summary,' ',descriptions,' ',keywords)";
    //run that query and set $str in the code above to:
    $str="stext rlike '";
    Altrnatively, you could put a full text index on the 3 fields, then use the match against in your condition. This would be better for larger data sets, smaller data sets, however do not do that well with full text indexing.


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
  •