all combinations in multi-word searches what to do

lse123 · #1 (**permalink**) 10-28-10, 05:08

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#%'

r937 · #2 (**permalink**) 10-28-10, 05:44

Quote:

Originally Posted by lse123

currently use:

looks okay to me

what's wrong with it?

lse123 · #3 (**permalink**) 10-28-10, 07:50

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?

r937 · #4 (**permalink**) 10-28-10, 07:56

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%'

lse123 · #5 (**permalink**) 10-28-10, 09:37

how understand the number of words submitted?

r937 · #6 (**permalink**) 10-28-10, 09:39

Quote:

Originally Posted by lse123

how understand the number of words submitted?

count the spaces

number of words = number of spaces + 1

lse123 · #7 (**permalink**) 10-28-10, 09:53

how implement the ORs in query. then to correct number, and after run?

r937 · #8 (**permalink**) 10-28-10, 09:56

Quote:

Originally Posted by lse123

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

lse123 · #9 (**permalink**) 10-28-10, 10:15

yes is CFML... I can not thing a way make it can you tell any hint?

r937 · #10 (**permalink**) 10-28-10, 10:24

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

Internet Services

Web Development

Digital Marketing

Consumer Tech