My site uses a text box to allow visitors to search products.
I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
e.g
megger
meg
mft megger (proper order in the database is megger mft)
mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code:
<%
Dim RSResults__param5
RSResults__param5 = "xxx"
If (Request("searchme") <> "") Then
RSResults__param5 = Request("searchme")
End If
%>
<%
Dim RSResults__param6
RSResults__param6 = "0"
If (Application("ClientID") <> "") Then
RSResults__param6 = Application("ClientID")
End If
%>
Set RSResults_cmd = Server.CreateObject ("ADODB.Command")
RSResults_cmd.ActiveConnection = MyConn
RSResults_cmd.CommandText = "SELECT FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, KEY_TBL.RANK, dbo.ClientProducts.ClientID FROM dbo.Products AS FT_TBL INNER JOIN FREETEXTTABLE(dbo.Products, Product, ?) AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY] INNER JOIN dbo.ClientProducts ON FT_TBL.ProductID = dbo.ClientProducts.ProductID WHERE (dbo.ClientProducts.ClientID = ?) ORDER BY " + orderby
RSResults_cmd.Prepared = true
RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param1", 200, 1, 255, "%" + RSResults__param5 + "%") ' adVarChar
RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param2", 5, 1, -1, RSResults__param6) ' adDouble
Set RSResults = RSResults_cmd.Execute
RSResults_numRows = 0
Any advice would be great.
Andy
dBforums
>
Fulltext Search DB Guru's please help!
12-06-11, 04:37
Linear Mode
