Results 1 to 10 of 10
  1. #1
    Join Date
    May 2007
    Posts
    139

    Unanswered: all combinations in multi-word searches what to do

    to use search whole site feature using database, to search keywords yyy zzz and keywords zzz yyy NOT differently but use all combinations in multi-word searches what to do?
    // but zzz yyy may get result, but the inverse in order words yyy zzz gives nothing.
    currently use:
    SELECT Products.*, Categories.* FROM Products INNER JOIN Categories ON Categories.Category_ID = Products.Category_ID WHERE Category LIKE '%#searchKeyword#%' OR Product_ID LIKE '%#searchKeyword#%' OR Product_Name LIKE '%#searchKeyword#%' OR Product_Description LIKE '%#searchKeyword#%'

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lse123 View Post
    currently use:
    looks okay to me

    what's wrong with it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2007
    Posts
    139
    If I search for "akamas flower" is ok return results, this order of words exist in database table.

    well, if search "flower akamas" returns NOTHING, ...Well?

    what to do to return the same as first keyword?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aha, okay, now i understand

    what you have to do is break up your submitted search string into whole words
    Code:
    WHERE Category            LIKE '%flower%' 
       OR Product_ID          LIKE '%flower%'
       OR Product_Name        LIKE '%flower%' 
       OR Product_Description LIKE '%flower%' 
       OR Category            LIKE '%akamas%' 
       OR Product_ID          LIKE '%akamas%'
       OR Product_Name        LIKE '%akamas%' 
       OR Product_Description LIKE '%akamas%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2007
    Posts
    139
    how understand the number of words submitted?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lse123 View Post
    how understand the number of words submitted?
    count the spaces

    number of words = number of spaces + 1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2007
    Posts
    139
    how implement the ORs in query. then to correct number, and after run?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lse123 View Post
    how implement the ORs in query. then to correct number, and after run?
    what application language are you using to produce a query that contains #searchKeyword#?

    looks like it could be coldfusion

    in any case, all of what you are asking is done with the application language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2007
    Posts
    139
    yes is CFML... I can not thing a way make it can you tell any hint?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's a hint -- split the submitted search string apart using the space as list delimiter, and process the words using CFLOOP
    Code:
    <!--- build the SELECT statement --->
    <CFOUTPUT>
    SELECT Products.*
         , Categories.* 
      FROM Products 
    INNER 
      JOIN Categories 
        ON Categories.Category_ID = Products.Category_ID 
     WHERE 0=1
    </CFOUTPUT>
    
    <!--- add the search conditions ---> 
    <CFLOOP INDEX="word" LIST="#searchstring#" DELIMITER=" "> 
    <CFOUTPUT>
       OR Category            LIKE '%#word#%' 
       OR Product_ID          LIKE '%#word#%'
       OR Product_Name        LIKE '%#word#%' 
       OR Product_Description LIKE '%#word#%' 
    </CFOUTPUT>
    </CFLOOP>
    please note: as a rule, we do not provide application language solutions in the mysql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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