Solved

create SQL with search terms separated by spaces

Posted on 2006-11-05
14
246 Views
Last Modified: 2010-04-25
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.
0
Comment
Question by:elliottbenzle
  • 7
  • 6
14 Comments
 
LVL 25

Expert Comment

by:Rouchie
ID: 17879562
You need to modify the SQL statement to be like this:

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("myTextBox"))  ' text box containing "my search words"
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
0
 
LVL 7

Expert Comment

by:jay_eire
ID: 17879570
Try this

SELECT * FROM shoppinglist
WHERE
(item like '%MMColParam%' OR description like '%MMColParam%')
0
 
LVL 4

Author Comment

by:elliottbenzle
ID: 17886938
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/consearchtest.asp" -->

<%
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.Recordset")
rssearchtest.ActiveConnection = MM_consearchtest_STRING
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocation = 2
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"><%=(rssearchtest.Fields.Item("ID").Value)%></td>
      <td width="92"><%=(rssearchtest.Fields.Item("item").Value)%></td>
      <td width="73"><%=(rssearchtest.Fields.Item("store").Value)%></td>
      <td width="73"><%=(rssearchtest.Fields.Item("address").Value)%></td>
      <td width="110"><%=(rssearchtest.Fields.Item("website").Value)%></td>
    </tr>
    <tr>
      <td colspan="5"><%=(rssearchtest.Fields.Item("description").Value)%></td>
    </tr>
  </table>
  <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 17887336
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.
0
 
LVL 4

Author Comment

by:elliottbenzle
ID: 17889080
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
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 17889166
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.
0
 
LVL 4

Author Comment

by:elliottbenzle
ID: 17889969
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.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 25

Expert Comment

by:Rouchie
ID: 17890163
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!
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 17890169
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
----------------------------------------------------------------------------------------------------------------------------
0
 
LVL 4

Author Comment

by:elliottbenzle
ID: 17891825
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/consearchtest.asp" -->

<%
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.Recordset")
rssearchtest.ActiveConnection = MM_consearchtest_STRING
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocation = 2
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"><%=(rssearchtest.Fields.Item("ID").Value)%></td>
      <td width="92"><%=(rssearchtest.Fields.Item("item").Value)%></td>
      <td width="73"><%=(rssearchtest.Fields.Item("store").Value)%></td>
      <td width="73"><%=(rssearchtest.Fields.Item("address").Value)%></td>
      <td width="110"><%=(rssearchtest.Fields.Item("website").Value)%></td>
    </tr>
    <tr>
      <td colspan="5"><%=(rssearchtest.Fields.Item("description").Value)%></td>
    </tr>
  </table>
  <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
0
 
LVL 25

Accepted Solution

by:
Rouchie earned 500 total points
ID: 17896105
My first thought is that the loop is cutting off too early when breaking up the words.  Try this:

Change

  For i = 0 to UBound(wordsArray) - 1

to

  For i = 0 to UBound(wordsArray)

If it doesn't work change it back and post the results of what happened.
0
 
LVL 4

Author Comment

by:elliottbenzle
ID: 17896737
Everything works great Rouchie, thanks for all your help.
0
 
LVL 4

Author Comment

by:elliottbenzle
ID: 17897024
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/consearchtest.asp" -->

<%
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.Recordset")
rssearchtest.ActiveConnection = MM_consearchtest_STRING
rssearchtest.Source = sqlString
rssearchtest.CursorType = 0
rssearchtest.CursorLocation = 2
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"><%=(rssearchtest.Fields.Item("ID").Value)%></td>
      <td width="92"><%=(rssearchtest.Fields.Item("item").Value)%></td>
      <td width="73"><%=(rssearchtest.Fields.Item("store").Value)%></td>
      <td width="73"><%=(rssearchtest.Fields.Item("address").Value)%></td>
      <td width="110"><%=(rssearchtest.Fields.Item("website").Value)%></td>
    </tr>
    <tr>
      <td colspan="5"><%=(rssearchtest.Fields.Item("description").Value)%></td>
    </tr>
  </table>
  <%
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  rssearchtest.MoveNext()
Wend
%>
</body>
</html>
<%
rssearchtest.Close()
Set rssearchtest = Nothing
%>
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 17897033
You're welome!  Thanks for staying patient while we cracked it :-)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
FINDNOCASE 2 536
Dreamweaver CS6 keyboard shortcut 8 1,028
Video Not Showing 8 91
Dreamweaver FTP Access 12 81
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now