If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ASP > Split Array for searching

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Posts: 116
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Posts: 116
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!!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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%')
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Posts: 116
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
%>
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Posts: 116
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
%>
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Posts: 116
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Posts: 116
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Posts: 116
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 04:02. Reason: addition
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Posts: 116
Still stuck :-(
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On