elliottbenzle
asked on
create SQL with search terms separated by spaces
I want to create a database search where the ueser types a list of works into a textfield then clicks the submit button on the form. The results page will then create an SQL which returns results that contain any of the words entered on any order. Here is my SQL right now.
SELECT *
FROM shoppinglist
WHERE item AND description LIKE '%MMColParam%'
This works but won't seperate words with spaces in between. So if the user enters "big heads" the SQL will return only the exact term "big heads" but not any entries which contain "big blah blah blah head" or just "big" or just "head".
Is there a way to make the variable (MMColParam) work to select seperate words and not the whole list of words? If you need more info please let me know. I'm using ASP VBScript.
SELECT *
FROM shoppinglist
WHERE item AND description LIKE '%MMColParam%'
This works but won't seperate words with spaces in between. So if the user enters "big heads" the SQL will return only the exact term "big heads" but not any entries which contain "big blah blah blah head" or just "big" or just "head".
Is there a way to make the variable (MMColParam) work to select seperate words and not the whole list of words? If you need more info please let me know. I'm using ASP VBScript.
Try this
SELECT * FROM shoppinglist
WHERE
(item like '%MMColParam%' OR description like '%MMColParam%')
SELECT * FROM shoppinglist
WHERE
(item like '%MMColParam%' OR description like '%MMColParam%')
ASKER
Rouchie,
I tried what you outlined but my coding isn't goood enough to make it work. For some reason if I enter one word the entire database is returned in the recordset. If I enter two words nothing is returned ever. The content of the search words entered doesn't seem to matter. Here is what I put together. Could you take a look at it for me? If you want to see the pages work they are here:
http://www.glowfishtest.com/search.asp
search page: <<<<<<<<<<<<<<<<<<<<<<<<<<
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!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="searchtest" name="searchtest" method="post" action="results.asp">
<label>
Search terms
<input name="test" type="text" id="test" />
</label>
<label> <br />
<br />
<input type="submit" name="Submit" value="Submit" />
</label>
</form>
</body>
</html>
results page <<<<<<<<<<<<<<<<<<<<<<<<<<
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/consearc
<%
Dim words
words = trim(request.form("test"))
words = replace(words, " ", ",")
%>
<%
Dim wordsArray
wordsArray = split(words,",")
%>
<%
Dim sqlString
sqlString = "SELECT * FROM shoppinglist WHERE "
For i = 0 to UBound(wordsArray) - 1
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4))
%>
<%
Dim rssearchtest
Dim rssearchtest_numRows
Set rssearchtest = Server.CreateObject("ADODB
rssearchtest.ActiveConnect
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocatio
rssearchtest.LockType = 1
rssearchtest.Open()
rssearchtest_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rssearchtest_numRows = rssearchtest_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>
<%
While ((Repeat1__numRows <> 0) AND (NOT rssearchtest.EOF))
%>
<table width="592" height="53" border="0" cellpadding="2" cellspacing="2">
<tr>
<td colspan="5">test number </td>
</tr>
<tr>
<td width="120"><%=(rssearchte
<td width="92"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="110"><%=(rssearchte
</tr>
<tr>
<td colspan="5"><%=(rssearchte
</tr>
</table>
<%
Repeat1__index=Repeat1__in
Repeat1__numRows=Repeat1__
rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
I tried what you outlined but my coding isn't goood enough to make it work. For some reason if I enter one word the entire database is returned in the recordset. If I enter two words nothing is returned ever. The content of the search words entered doesn't seem to matter. Here is what I put together. Could you take a look at it for me? If you want to see the pages work they are here:
http://www.glowfishtest.com/search.asp
search page: <<<<<<<<<<<<<<<<<<<<<<<<<<
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!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="searchtest" name="searchtest" method="post" action="results.asp">
<label>
Search terms
<input name="test" type="text" id="test" />
</label>
<label> <br />
<br />
<input type="submit" name="Submit" value="Submit" />
</label>
</form>
</body>
</html>
results page <<<<<<<<<<<<<<<<<<<<<<<<<<
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/consearc
<%
Dim words
words = trim(request.form("test"))
words = replace(words, " ", ",")
%>
<%
Dim wordsArray
wordsArray = split(words,",")
%>
<%
Dim sqlString
sqlString = "SELECT * FROM shoppinglist WHERE "
For i = 0 to UBound(wordsArray) - 1
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4))
%>
<%
Dim rssearchtest
Dim rssearchtest_numRows
Set rssearchtest = Server.CreateObject("ADODB
rssearchtest.ActiveConnect
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocatio
rssearchtest.LockType = 1
rssearchtest.Open()
rssearchtest_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rssearchtest_numRows = rssearchtest_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>
<%
While ((Repeat1__numRows <> 0) AND (NOT rssearchtest.EOF))
%>
<table width="592" height="53" border="0" cellpadding="2" cellspacing="2">
<tr>
<td colspan="5">test number </td>
</tr>
<tr>
<td width="120"><%=(rssearchte
<td width="92"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="110"><%=(rssearchte
</tr>
<tr>
<td colspan="5"><%=(rssearchte
</tr>
</table>
<%
Repeat1__index=Repeat1__in
Repeat1__numRows=Repeat1__
rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
Okay, what we'll do is output the SQL command to the screen to find out where the bug is.
After this line of code:
sqlString = Left(sqlString, (Len(sqlString) - 4))
Add this line:
Response.Write("<hr/>" & sqlString & "<hr/>")
Then run the pages. On the results screen you'll see the SQL command outputted between 2 horizontal rule lines. Copy and paste that search command into this post so I can see it please.
After this line of code:
sqlString = Left(sqlString, (Len(sqlString) - 4))
Add this line:
Response.Write("<hr/>" & sqlString & "<hr/>")
Then run the pages. On the results screen you'll see the SQL command outputted between 2 horizontal rule lines. Copy and paste that search command into this post so I can see it please.
ASKER
rouchie, thanks for your help
the database has an entry with the item name 'sour cream' ideally this entry will come up if you enter the word 'sour'
I entered 'sour' in the search box and this is the result
--------------------------
SELECT * FROM shoppinglist WH
--------------------------
with the entire database displayed
I entered 'sour cream' in the search box and this is the result
--------------------------
SELECT * FROM shoppinglist WHERE item LIKE 'sour' AND description LIKE 'sour'
--------------------------
with no results displayed
Thanks again for any help
the database has an entry with the item name 'sour cream' ideally this entry will come up if you enter the word 'sour'
I entered 'sour' in the search box and this is the result
--------------------------
SELECT * FROM shoppinglist WH
--------------------------
with the entire database displayed
I entered 'sour cream' in the search box and this is the result
--------------------------
SELECT * FROM shoppinglist WHERE item LIKE 'sour' AND description LIKE 'sour'
--------------------------
with no results displayed
Thanks again for any help
Okay I see what's happening. The SQL search phrase isn't quite correct, so in the code where this is generated (this line):
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
change to this:
sqlString = sqlString & "item LIKE '%" & wordsArray(i) & "%' AND description LIKE '%" & wordsArray(i) & "%' AND"
then try run the page again.
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
change to this:
sqlString = sqlString & "item LIKE '%" & wordsArray(i) & "%' AND description LIKE '%" & wordsArray(i) & "%' AND"
then try run the page again.
ASKER
it's getting closer
now, when I enter 'sour cream' I get this
--------------------------
SELECT * FROM shoppinglist WHERE item LIKE '%sour%' AND description LIKE '%sour%'
--------------------------
with the proper database record listed
however if I enter 'sour' or any single word I get this
--------------------------
SELECT * FROM shoppinglist WH
--------------------------
with entire database listed
and if I enter 'sour cream sour' or any three search terms I get this error message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'item LIKE '%sour%' AND description LIKE '%sour%' ANDitem LIKE '%cream%' AND description LIKE '%cream%''.
/searchtest/results.asp, line 33
If this is getting too complicated please let me know and I'll purchase an extension from somewhere, I know this is getting a little extensive, thanks for your help.
now, when I enter 'sour cream' I get this
--------------------------
SELECT * FROM shoppinglist WHERE item LIKE '%sour%' AND description LIKE '%sour%'
--------------------------
with the proper database record listed
however if I enter 'sour' or any single word I get this
--------------------------
SELECT * FROM shoppinglist WH
--------------------------
with entire database listed
and if I enter 'sour cream sour' or any three search terms I get this error message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'item LIKE '%sour%' AND description LIKE '%sour%' ANDitem LIKE '%cream%' AND description LIKE '%cream%''.
/searchtest/results.asp, line 33
If this is getting too complicated please let me know and I'll purchase an extension from somewhere, I know this is getting a little extensive, thanks for your help.
Don't worry about it getting complicated, it's just a case of trial and error unfortunately. Okay to fix the last error we need to just add a space character between each loop.
Change:
sqlString = sqlString & "item LIKE '%" & wordsArray(i) & "%' AND description LIKE '%" & wordsArray(i) & "%' AND"
to:
sqlString = sqlString & " item LIKE '%" & wordsArray(i) & "%' AND description LIKE '%" & wordsArray(i) & "%' AND"
^
^
Simple space so that "ANDitem" becomes "AND item" in the SQL query
Then we need to add some more logic to check that more than 1 word was actually entered before we start breaking them up. So this code:
--------------------------
sqlString = "SELECT * FROM shoppinglist WHERE "
For i = 0 to UBound(wordsArray) - 1 ' loop through array and build search statement
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4)) ' remove the trailing AND from the SQL statement
--------------------------
becomes this:
--------------------------
sqlString = "SELECT * FROM shoppinglist"
If UBound(wordsArray) > 0 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray) - 1
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4))
End If
--------------------------
The logic now dictates that if there is more than 1 word entered, then we will break up the words into an array and form the SQL command. Then we will trim off the AND at the end of the statement.
Otherwise we'll just perform the query as-is. Contact me if the last bit isn't what you want!
Change:
sqlString = sqlString & "item LIKE '%" & wordsArray(i) & "%' AND description LIKE '%" & wordsArray(i) & "%' AND"
to:
sqlString = sqlString & " item LIKE '%" & wordsArray(i) & "%' AND description LIKE '%" & wordsArray(i) & "%' AND"
^
^
Simple space so that "ANDitem" becomes "AND item" in the SQL query
Then we need to add some more logic to check that more than 1 word was actually entered before we start breaking them up. So this code:
--------------------------
sqlString = "SELECT * FROM shoppinglist WHERE "
For i = 0 to UBound(wordsArray) - 1 ' loop through array and build search statement
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4)) ' remove the trailing AND from the SQL statement
--------------------------
becomes this:
--------------------------
sqlString = "SELECT * FROM shoppinglist"
If UBound(wordsArray) > 0 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray) - 1
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4))
End If
--------------------------
The logic now dictates that if there is more than 1 word entered, then we will break up the words into an array and form the SQL command. Then we will trim off the AND at the end of the statement.
Otherwise we'll just perform the query as-is. Contact me if the last bit isn't what you want!
Sorry there's a typo, new version:
--------------------------
sqlString = "SELECT * FROM shoppinglist"
If UBound(wordsArray) > 0 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray) - 1
sqlString = sqlString & " item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4))
End If
--------------------------
--------------------------
sqlString = "SELECT * FROM shoppinglist"
If UBound(wordsArray) > 0 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray) - 1
sqlString = sqlString & " item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4))
End If
--------------------------
ASKER
Dear Rouchie,
I think this is very close. I changed the sql a little to return all results with the search terms (OR '% %'):
sqlString = sqlString & " item LIKE '%" & wordsArray(i) & "%' OR description LIKE '%" & wordsArray(i) & "%' OR"
For some reson the SQL is cutting off the last search word. example:
enter 'sour' results
--------------------------
SELECT * FROM shoppinglist
--------------------------
enter 'sour cream' results
--------------------------
SELECT * FROM shoppinglist WHERE item LIKE 'sour' OR description LIKE 'sour'
--------------------------
enter 'sour cream wobble' results
--------------------------
SELECT * FROM shoppinglist WHERE item LIKE 'sour' OR description LIKE 'sour' AND item LIKE 'cream' OR description LIKE 'cream'
--------------------------
Below is everything that I now have. Any idea as to what is causing the problem?
Thanks
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/consearc
<%
Dim words
words = trim(request.form("test"))
words = replace(words, " ", ",")
%>
<%
Dim wordsArray
wordsArray = split(words,",")
%>
<%
Dim sqlString
sqlString = "SELECT * FROM shoppinglist"
If UBound(wordsArray) > 0 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray) - 1
sqlString = sqlString & " item LIKE '%" & wordsArray(i) & "%' OR description LIKE '%" & wordsArray(i) & "%' OR"
Next
sqlString = Left(sqlString, (Len(sqlString) - 3))
End If
Response.Write("<hr/>" & sqlString & "<hr/>")
%>
<%
Dim rssearchtest
Dim rssearchtest_numRows
Set rssearchtest = Server.CreateObject("ADODB
rssearchtest.ActiveConnect
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocatio
rssearchtest.LockType = 1
rssearchtest.Open()
rssearchtest_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rssearchtest_numRows = rssearchtest_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>
<%
While ((Repeat1__numRows <> 0) AND (NOT rssearchtest.EOF))
%>
<table width="592" height="53" border="0" cellpadding="2" cellspacing="2">
<tr>
<td colspan="5">test number </td>
</tr>
<tr>
<td width="120"><%=(rssearchte
<td width="92"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="110"><%=(rssearchte
</tr>
<tr>
<td colspan="5"><%=(rssearchte
</tr>
</table>
<%
Repeat1__index=Repeat1__in
Repeat1__numRows=Repeat1__
rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
I think this is very close. I changed the sql a little to return all results with the search terms (OR '% %'):
sqlString = sqlString & " item LIKE '%" & wordsArray(i) & "%' OR description LIKE '%" & wordsArray(i) & "%' OR"
For some reson the SQL is cutting off the last search word. example:
enter 'sour' results
--------------------------
SELECT * FROM shoppinglist
--------------------------
enter 'sour cream' results
--------------------------
SELECT * FROM shoppinglist WHERE item LIKE 'sour' OR description LIKE 'sour'
--------------------------
enter 'sour cream wobble' results
--------------------------
SELECT * FROM shoppinglist WHERE item LIKE 'sour' OR description LIKE 'sour' AND item LIKE 'cream' OR description LIKE 'cream'
--------------------------
Below is everything that I now have. Any idea as to what is causing the problem?
Thanks
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/consearc
<%
Dim words
words = trim(request.form("test"))
words = replace(words, " ", ",")
%>
<%
Dim wordsArray
wordsArray = split(words,",")
%>
<%
Dim sqlString
sqlString = "SELECT * FROM shoppinglist"
If UBound(wordsArray) > 0 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray) - 1
sqlString = sqlString & " item LIKE '%" & wordsArray(i) & "%' OR description LIKE '%" & wordsArray(i) & "%' OR"
Next
sqlString = Left(sqlString, (Len(sqlString) - 3))
End If
Response.Write("<hr/>" & sqlString & "<hr/>")
%>
<%
Dim rssearchtest
Dim rssearchtest_numRows
Set rssearchtest = Server.CreateObject("ADODB
rssearchtest.ActiveConnect
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocatio
rssearchtest.LockType = 1
rssearchtest.Open()
rssearchtest_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rssearchtest_numRows = rssearchtest_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>
<%
While ((Repeat1__numRows <> 0) AND (NOT rssearchtest.EOF))
%>
<table width="592" height="53" border="0" cellpadding="2" cellspacing="2">
<tr>
<td colspan="5">test number </td>
</tr>
<tr>
<td width="120"><%=(rssearchte
<td width="92"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="110"><%=(rssearchte
</tr>
<tr>
<td colspan="5"><%=(rssearchte
</tr>
</table>
<%
Repeat1__index=Repeat1__in
Repeat1__numRows=Repeat1__
rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Everything works great Rouchie, thanks for all your help.
ASKER
A quick comment,
If anyone is trying to repeat this code you have to make a small change to the final code. the line:
If UBound(wordsArray) > 0 then
needs to be changed to:
If UBound(wordsArray) > - 1 then
otherwise the search will return not work prpoerly for any single word entries. Below is the code in it's entiretly, including the seach and the results page. Thanks again Rouchie for a great search code.
Search page <<<<<<<<<<<<<<<<<<<<<<<<<<
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!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="searchtest" name="searchtest" method="post" action="results.asp">
<label>
Search terms
<input name="test" type="text" id="test" />
<br />
</label>
<label><br />
<input type="submit" name="Submit" value="Submit" />
</label>
</form>
</body>
</html>
results page <<<<<<<<<<<<<<<<<<<<<<<<<<
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/consearc
<%
Dim words
words = trim(request.form("test"))
words = replace(words, " ", ",")
%>
<%
Dim wordsArray
wordsArray = split(words,",")
%>
<%
Dim sqlString
sqlString = "SELECT * FROM shoppinglist"
If UBound(wordsArray) > - 1 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray)
sqlString = sqlString & " item LIKE '%" & wordsArray(i) & "%' OR description LIKE '%" & wordsArray(i) & "%' OR"
Next
sqlString = Left(sqlString, (Len(sqlString) - 3))
End If
Response.Write("<hr/>" & sqlString & "<hr/>")
%>
<%
Dim rssearchtest
Dim rssearchtest_numRows
Set rssearchtest = Server.CreateObject("ADODB
rssearchtest.ActiveConnect
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocatio
rssearchtest.LockType = 1
rssearchtest.Open()
rssearchtest_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rssearchtest_numRows = rssearchtest_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>
<%
While ((Repeat1__numRows <> 0) AND (NOT rssearchtest.EOF))
%>
<table width="592" height="53" border="0" cellpadding="2" cellspacing="2">
<tr>
<td colspan="5">test number </td>
</tr>
<tr>
<td width="120"><%=(rssearchte
<td width="92"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="110"><%=(rssearchte
</tr>
<tr>
<td colspan="5"><%=(rssearchte
</tr>
</table>
<%
Repeat1__index=Repeat1__in
Repeat1__numRows=Repeat1__
rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
If anyone is trying to repeat this code you have to make a small change to the final code. the line:
If UBound(wordsArray) > 0 then
needs to be changed to:
If UBound(wordsArray) > - 1 then
otherwise the search will return not work prpoerly for any single word entries. Below is the code in it's entiretly, including the seach and the results page. Thanks again Rouchie for a great search code.
Search page <<<<<<<<<<<<<<<<<<<<<<<<<<
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!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="searchtest" name="searchtest" method="post" action="results.asp">
<label>
Search terms
<input name="test" type="text" id="test" />
<br />
</label>
<label><br />
<input type="submit" name="Submit" value="Submit" />
</label>
</form>
</body>
</html>
results page <<<<<<<<<<<<<<<<<<<<<<<<<<
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/consearc
<%
Dim words
words = trim(request.form("test"))
words = replace(words, " ", ",")
%>
<%
Dim wordsArray
wordsArray = split(words,",")
%>
<%
Dim sqlString
sqlString = "SELECT * FROM shoppinglist"
If UBound(wordsArray) > - 1 then
sqlString = sqlString & " WHERE "
For i = 0 to UBound(wordsArray)
sqlString = sqlString & " item LIKE '%" & wordsArray(i) & "%' OR description LIKE '%" & wordsArray(i) & "%' OR"
Next
sqlString = Left(sqlString, (Len(sqlString) - 3))
End If
Response.Write("<hr/>" & sqlString & "<hr/>")
%>
<%
Dim rssearchtest
Dim rssearchtest_numRows
Set rssearchtest = Server.CreateObject("ADODB
rssearchtest.ActiveConnect
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocatio
rssearchtest.LockType = 1
rssearchtest.Open()
rssearchtest_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
rssearchtest_numRows = rssearchtest_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>
<%
While ((Repeat1__numRows <> 0) AND (NOT rssearchtest.EOF))
%>
<table width="592" height="53" border="0" cellpadding="2" cellspacing="2">
<tr>
<td colspan="5">test number </td>
</tr>
<tr>
<td width="120"><%=(rssearchte
<td width="92"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="73"><%=(rssearchtes
<td width="110"><%=(rssearchte
</tr>
<tr>
<td colspan="5"><%=(rssearchte
</tr>
</table>
<%
Repeat1__index=Repeat1__in
Repeat1__numRows=Repeat1__
rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
You're welome! Thanks for staying patient while we cracked it :-)
SELECT
*
FROM
shoppinglist
WHERE
item LIKE '%MMColParam1%' AND
description LIKE '%MMColParam1%' AND
item LIKE '%MMColParam2%' AND
description LIKE '%MMColParam2%' AND
item LIKE '%MMColParam3%' AND
description LIKE '%MMColParam3%'
Because of the unknown number of words a user can enter, you will have to use a dynamic SQL statement to make this work. DW can not do this for you because it is not powerful enough.
The code is something like, based on a user submitting the phrase "my search words"
Dim words = trim(request.form("myTextB
words = replace(words, " ", ",") ' turns "my search words" into "my,search,words"
Dim wordsArray
wordsArray = split(words,",") ' put the words into an array
Dim sqlString
sqlString = "SELECT * FROM shoppinglist WHERE "
For i = 0 to UBound(wordsArray) - 1 ' loop through array and build search statement
sqlString = sqlString & "item LIKE '" & wordsArray(i) & "' AND description LIKE '" & wordsArray(i) & "' AND"
Next
sqlString = Left(sqlString, (Len(sqlString) - 4)) ' remove the trailing AND from the SQL statement
'The whole search statement in now contained in sqlString