Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2008
    Posts
    119

    Split Array for searching

    Hi all.
    Can anyone supply some sample code please?
    I'm trying to take the values from a product search box, split each word in to an Array and then using the Array in my SQL Select Statement.

    Select * from table
    Where Product = Array(0) OR Array(1) OR Array(2), etc.... depending on how many words are in the Array.

    My Code so far:
    Code:
    <% Dim SearchArray
    SearchArray = Split(Request("searchme"), " ")
    For Each x in SearchArray
    response.write(x & " ")
    
    Next
    %>
    My Select Statament
    Code:
    SELECT Products.*, Categories.Category  FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID  WHERE Product LIKE
    This prints out the search phrase, but how do i use this in my SQL Select Statement using the OR between each search word?

    Hope someone can help out :-)

    Thanks
    Andy

  2. #2
    Join Date
    Feb 2008
    Posts
    119
    I've also tried this

    Code:
    Select * from Products
    Where ProductID IN ('48', '133', '212');
    go
    Which works fine, but if i use
    Code:
    Select * from Products
    Where Product IN ('megger', 'mft1720', 'tester');
    go
    I get no results, even though i know the words are in part of the Product titles.

    Help!!

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    So assuming your code is mostly right (haven't played with ASP for a bit)

    This code should do what you want...
    Code:
    <% Dim SearchArray, sqlString, items
    SearchArray = Split(Request("searchme"), " ")
    sqlString = "SELECT Products.*, Categories.Category  FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID  WHERE"
    items = 0
    For Each x in SearchArray
        if(items>0) then
            sqlString = sqlString + " or "
        end if
        items = items + 1
        sqlString = sqlString + "(Product like '%" + x + "'%)"
    Next
    response.write sqlString
    %>
    If we submit the searchme value of "result1 result2" this should create a string that looks something like
    Code:
    SELECT Products.*, Categories.Category  FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID  WHERE (Product LIKE '%result1%') or (Product LIKE '%result2%')

  4. #4
    Join Date
    Feb 2008
    Posts
    119
    hi rokslide sorry to be a pain :-)

    I still can't get my head around this one!

    I'm using Dreamweaver and trying to apply the code you supplied to the Dreamweaver code. I'm getting a 'type mismatch' error on line 28

    Code:
    RSResults__MMColParam = RSResults__MMColParam + "(Product Like '%" + x + "'%)"
    I'm using Dreamweaver code to prevent SQL Injection

    Code:
    <%
    Dim RSResults__MMColParam
    RSResults__MMColParam = "1"
    If (Request.Form("searchme") <> "") Then 
      RSResults__MMColParam = Split(Request.Form("searchme"), " ")
    End If
    %>
    <%
    Dim RSResults
    Dim RSResults_cmd
    Dim RSResults_numRows
    Dim items
    
    Set RSResults_cmd = Server.CreateObject ("ADODB.Command")
    RSResults_cmd.ActiveConnection = MM_shoppingcart_STRING
    RSResults_cmd.CommandText = "SELECT * FROM dbo.Products WHERE"
    items=0
    For Each x in RSResults__MMColParam
    if(itens>0) Then
    	RSResults__MMColParam = RSResults__MMColParam + " or "
    	end if
    	items = items + 1
    	RSResults__MMColParam = RSResults__MMColParam + "(Product Like '%" + x + "'%)"
    Next	 
    RSResults_cmd.Prepared = true
    RSResults_cmd.Parameters.Append RSResults_cmd.CreateParameter("param1", 200, 1, 100, "%" + RSResults__MMColParam + "%") ' adVarChar
    
    Set RSResults = RSResults_cmd.Execute
    RSResults_numRows = 0
    %>

  5. #5
    Join Date
    Feb 2008
    Posts
    119
    Or if i use this code, i get this error
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.

    Line 33

    Code:
    RSResults.Open()
    Code:
    <%
    Dim RSResults__MMColParam, SearchArray, items, sqlString 
    RSResults__MMColParam = "0"
    %>
    
    <%
    If (Request("searchme") <> "") Then 
    SearchArray = Split(Request("searchme"), " ")
    sqlstring = "SELECT Products.*, Categories.Category  FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID  WHERE"
    items = 0
    For Each x in SearchArray
        if(items>0) then
            sqlString = sqlString + " or "
        end if
        items = items + 1
        sqlString = sqlString + "(Product like '%" + x + "'%)"
    Next
    %> 
    
    <%
    Set RSResults = Server.CreateObject("ADODB.Recordset")
    RSResults.ActiveConnection = MM_shoppingcart_STRING
    RSResults.Source = sqlString
    RSResults.CursorType = 0
    RSResults.CursorLocation = 2
    RSResults.LockType = 1
    RSResults.Open()
    
    RSResults_numRows = 0
    End If
    %>

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    to find out what is happening with the second error try this...
    Code:
    <%
    Dim RSResults__MMColParam, SearchArray, items, sqlString 
    RSResults__MMColParam = "0"
    %>
    
    <%
    If (Request("searchme") <> "") Then 
    SearchArray = Split(Request("searchme"), " ")
    sqlstring = "SELECT Products.*, Categories.Category  FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID  WHERE"
    items = 0
    For Each x in SearchArray
        if(items>0) then
            sqlString = sqlString + " or "
        end if
        items = items + 1
        sqlString = sqlString + "(Product like '%" + x + "%')"
    Next
    %> 
    
    <%
    Response.write sqlString
    Set RSResults = Server.CreateObject("ADODB.Recordset")
    RSResults.ActiveConnection = MM_shoppingcart_STRING
    RSResults.Source = sqlString
    RSResults.CursorType = 0
    RSResults.CursorLocation = 2
    RSResults.LockType = 1
    RSResults.Open()
    
    RSResults_numRows = 0
    End If
    This will output the sql statement before it executes and should allow you to find the syntax issue.

    Actually, as I was proofing this I saw the problem. You had '% instead of %' at the end of your like statement. I have corrected it above.

  7. #7
    Join Date
    Feb 2008
    Posts
    119
    I'm back

    Had to leave this problem, but revisiting it now to try and sort it out, ouch!

    Ok firstly, thanks to rokslide for your previous advice. My code has changed since my last post.
    I'm now using a stored procedure for my select statement.
    Bare in mind i am using FullTestSearch in MS SQL Server and the Select Statement in my Stored Procedure is using CONTAINSTABLE

    Code:
    USE [mydatabase]
    GO
    /****** Object:  StoredProcedure [dbo].[RSSResults]    Script Date: 01/03/2012 16:15:37 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		
    -- Create date: 
    -- Description:	
    -- =============================================
    ALTER PROCEDURE [dbo].[RSSResults] 
    	-- Add the parameters for the stored procedure here
    	@p1 int, 
    	@p2 nvarchar(1024)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	SELECT     FT_TBL.ProductID, FT_TBL.Product, FT_TBL.Image, FT_TBL.Price, dbo.ClientProducts.ClientID, dbo.ClientOffers.OfferID
    FROM         dbo.Products AS FT_TBL LEFT OUTER JOIN
                          CONTAINSTABLE(dbo.Products, Product, @p2) AS KEY_TBL ON FT_TBL.ProductID = KEY_TBL.[KEY] LEFT OUTER JOIN
                          dbo.ClientProducts ON FT_TBL.ProductID = dbo.ClientProducts.ProductID LEFT OUTER JOIN
                          dbo.ClientOffers ON FT_TBL.ProductID = dbo.ClientOffers.ProductID AND dbo.ClientOffers.ClientID = dbo.ClientProducts.ClientID
    WHERE     (dbo.ClientProducts.ClientID = @p1) AND (KEY_TBL.RANK > 0)
    END
    I have a simple ASP page to display the search results

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
    <!--#include file="myconn.asp" -->
    <%
    
    Dim Command1__p1
    Command1__p1 = "0"
    if(Application("ClientID") <> "") then Command1__p1 = Application("ClientID")
    
    Dim Command1__p2
    Command1__p2 = "xxx"
    if(Request("searchme") <> "") then Command1__p2 = Request("searchme")
    
    %>
    <%
    
    set Command1 = Server.CreateObject("ADODB.Command")
    Command1.ActiveConnection = myconn
    Command1.CommandText = "dbo.RSSResults"
    Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
    Command1.Parameters.Append Command1.CreateParameter("@p1", 5, 1,-1,Command1__p1)
    Command1.Parameters.Append Command1.CreateParameter("@p2", 200, 1,1024,Command1__p2)
    Command1.CommandType = 4
    Command1.CommandTimeout = 0
    Command1.Prepared = true
    set RSResults = Command1.Execute
    RSResults_numRows = 0
    
    %>
    <%
    Dim Repeat1__numRows
    Dim Repeat1__index
    
    Repeat1__numRows = -1
    Repeat1__index = 0
    RSResults_numRows = RSResults_numRows + Repeat1__numRows
    %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    </head>
    
    <body>
    <form id="form1" name="form1" method="post" action="searchme2.asp">
      <label>
      <input name="searchme" type="text" id="searchme" size="50" />
      </label>
      <label>
      <input type="submit" name="Submit" value="Submit" />
      </label>
    </form>
    <p>
      <% Response.write (Command1__p2) %>
      <br />
      <% 
    While ((Repeat1__numRows <> 0) AND (NOT RSResults.EOF)) 
    %>
        <%= RSResults.Fields.Item("Product").Value %><br />
        <% 
      Repeat1__index=Repeat1__index+1
      Repeat1__numRows=Repeat1__numRows-1
      RSResults.MoveNext()
    Wend
    %>
    </p>
    </body>
    </html>
    This simple page works if i use a single search word, but if is use a two word search phrase, it errors out with:

    Code:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error near 'tester' in the full-text search condition 'electrical tester'.
    I know i have to 'split' the search phrase, but i don't know how to use it in my code :-(

    Please help before i implode :-)

    Andy

  8. #8
    Join Date
    Feb 2008
    Posts
    119
    ok, if i wrap the @p2 varialbe in my stored procedure with double quotes like this
    set @p2 = '"' + @p2 + '"'

    I can search on a phrase without an error.
    This is still useless though as i want to be able to search on any word, in any order and also multiple words (phrases).



    Andy

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Hi Andy,

    Give me some scenarios of exactly how you want your searching to work, eg if you supply this value the results will include this, this and that, but not them....

    Searching in an elegant manner is a tricky task when you want to search by multiple phrases/criteria.

    If you can give soem scenarios we might be able to come up with a sweet solution for you.

  10. #10
    Join Date
    Feb 2008
    Posts
    119
    Hi rokslide :-)

    I'm using one search box (text box) in a form.
    I search on just one field in my products table which is the product Title.
    Typical product Title is:
    Megger MFT1720 Multifunction Tester

    I want a visitor to be able enter typical search values of the following
    MFT1720
    Megger Multifunction Tester
    Megger
    Megg
    MFT
    Multifunction Tester
    Multifunction

    And also any of the search values above, regardless of the order each word is in. e.g
    MFT1720 Tester
    MFT1720 Megger
    MFT Tester
    MFT Megg
    etc......

    I am using CONTAINSTABLE and use the RANK field to restrict the number of records and to Order then by RANK.

    Thank you yet again for responding to my plight :-)

    Andy

    PS I am assuming that by using FULLTEXT SEARCH and CONTAINSTABLE, the RANK field will provide me with the intelligence and importance of the records returned.
    Last edited by AndyJay; 01-05-12 at 05:02. Reason: addition

  11. #11
    Join Date
    Feb 2008
    Posts
    119
    Still stuck :-(

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Post your code for what you have and I will take a look at it.

Posting Permissions

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