By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,751 Members | 1,088 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,751 IT Pros & Developers. It's quick & easy.

Search function

100+
P: 347
can ayone point me in the right direction for this......

I am trying to write a search function for a page to return results from a list of mp3 filenames, i can search for one word no problem but i want to search for multiple words and pull out anthing that has either of the words all of the owrds etc.

I know you used to be able to do this with dreamweaver but i can t seem to do it with cs3????
May 18 '09 #1
Share this Question
Share on Google+
26 Replies

GazMathias
Expert 100+
P: 200
Hi,

You can use the split() function to break apart the words and loop each word into your WHERE clause.

For example:

Expand|Select|Wrap|Line Numbers
  1. Dim strSearch, myarray, strSQL, strSQLExtra
  2. strSearch =  "some words to search for"
  3.  
  4. myarray = split(strSearch, " ")
  5.  
  6. strSQL = "SELECT STUFF FROM TABLE WHERE "
  7.  
  8. For each item in myarray
  9. strSQLExtra = strSQLExtra & " (SomeField Like '*" & item & "*') OR "
  10. next
  11.  
  12. strSQLExtra = left(strSQLExtra, len(strSQLExtra)-3) ' Chops off the last "OR "
  13.  
  14. strSQL = strSQL & strSQLExtra
  15.  
  16. Response.Write strSQL
  17.  
End Result:

SELECT STUFF FROM TABLE WHERE (SomeField Like '*some*') OR (SomeField Like '*words*') OR (SomeField Like '*to*') OR (SomeField Like '*search*') OR (SomeField Like '*for*')

Gaz.
May 18 '09 #2

100+
P: 347
so would this return results that had just the word some or the words some and for etc.

also how do i use the sql inmy usual code i usually code my database connections as follows

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Response.Expires=0
  3. Set Connection=Server.CreateObject("ADODB.Connection")
  4. DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/database.mdb") & ";"
  5. .Open DatabaseDetails
  6. Query="SELECT * FROM celebs  where idnumber =" &actorid& " order by orderno"
  7. Set Recordset=Connection.execute(Query)
  8. %>
just cant figure out how to use the generated sql in this
May 18 '09 #3

GazMathias
Expert 100+
P: 200
@colinod
I can't either from the code you posted above, just doesn't seem to relate to what you posted previously. In particular you refer to searching in song filenames in your previous post, which the example above would cover, however in your second post you are implicitly passing the actor id over.

Am I missing something?

At any rate, it was only an example and I was just illustrating one possible direction, namely using the split() function.

so would this return results that had just the word some or the words some and for etc.
http://www.w3schools.com/SQL/sql_like.asp

Gaz
May 18 '09 #4

100+
P: 347
sorry code should have read

Expand|Select|Wrap|Line Numbers
  1. <% 
  2. Response.Expires=0 
  3. Set Connection=Server.CreateObject("ADODB.Connection") 
  4. DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/database.mdb") & ";" 
  5. .Open DatabaseDetails 
  6. Query="SELECT * FROM mp3  where idnumber =" &mp3id& " order by orderno" 
  7. Set Recordset=Connection.execute(Query) 
  8. %> 
im just not sure how to get the sql from your code example intothe sql above
May 20 '09 #5

GazMathias
Expert 100+
P: 200
Query="SELECT * FROM mp3 where idnumber =" &mp3id& " order by orderno"
Again, this does not seem to relate to searching in file names.

The idea is to use WHERE somefield LIKE '*" & word & "*'"

The * is a wildcard, so read above as find a record where somefield has anything then word then anything in it.

Some examples of results would include:

wordsmith
fore word
my word!
word search

etc!
May 20 '09 #6

100+
P: 347
ok its just me i have it wroking to search 1 field with the following

Expand|Select|Wrap|Line Numbers
  1. <%If Len(Request.Form) > 0 Then
  2.     term = Trim(Request.Form("search"))
  3.     term = Replace(term,"'","''")
  4.  
  5.     If InStr(term," ") > 0 Then
  6.         term = Split(term," ")
  7.  
  8.         For x=0 to Ubound(term)
  9.             If x > 0 Then
  10.                 clause = clause & " AND"
  11.             End If
  12.         clause = clause & " filename LIKE '%" & term(x) & "%'"
  13.         Next
  14.     Else
  15.         clause = " filename LIKE '%" & term & "%'"
  16.     End If
  17.     'sql = " WHERE " & clause
  18. sql = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE" & clause
  19. 'Response.Write sql
  20. End If 
  21. %>
  22. <%
  23. Response.Expires=0
  24. Set yaketyConnection=Server.CreateObject("ADODB.Connection")
  25. DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketynew.mdb") & ";"
  26. yaketyConnection.Open DatabaseDetails
  27. yaketyQuery=sql
  28. Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)
  29. %>
What i want to know is it easy to search more than 1 field in the database, i have 2 fields i would like to search and return results if the word is in any of them, i think i am trying to do this right.

I want to have 2 fields the title of the mp3 and then a linked field that will contain various words that describe the file contents and i want to search both of them so the fields are called filename and description could i just change the clause = lines and add an OR to them?
May 20 '09 #7

GazMathias
Expert 100+
P: 200
To search in more than one field:

Expand|Select|Wrap|Line Numbers
  1. WHERE ((field 1 like "somevalue") OR (field 2 like "somevalue"))
Gaz.
May 20 '09 #8

100+
P: 347
thanks for that, how would i change the code just to return everything that contains all the words but not necessarily in the order typed or with words inbetween

e.g

type in female african accent

get returned anything that has all 3 words in its description or in the title
May 20 '09 #9

GazMathias
Expert 100+
P: 200
You need to combine the two principles, something like:

Expand|Select|Wrap|Line Numbers
  1. Dim strSearch, myarray, strSQL, strSQLExtra
  2. strSearch =  "female african accent"
  3.  
  4. 'Loop is now in a function, so that we can feed it each field name.
  5. Function addparams(fieldname,wordArray) 
  6. Dim tmp
  7. tmp = ""
  8.     For each item in wordArray
  9.     tmp = tmp & " (" & fieldname & " Like '%" & item & "%') AND "
  10.     next
  11.     tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND "
  12.     addparams = tmp
  13. End Function
  14.  
  15. 'Build the word array
  16. myarray = split(strSearch, " ")
  17.  
  18. Build the SQL 
  19. strSQL = "SELECT STUFF FROM TABLE WHERE ("
  20.  
  21. strSQL = strSQL & addparams("field one", myarray) ' change field one!
  22.  
  23. strSQL = strSQL & ") OR ("
  24.  
  25. strSQL = strSQL & addparams("field two", myarray) ' change field two!
  26.  
  27. strSQL = strSQL & ");"
  28.  
Not tested that on a real database but the SQL looks OK.

Gaz
May 20 '09 #10

100+
P: 347
thanks that works fine, i think i might want to give the choice with a bullet on the form, ie all the words or any of the words....any ideas
May 21 '09 #11

GazMathias
Expert 100+
P: 200
You would need to modify the loop to be able to change the ANDs to ORs at will:

First Get the value from the form:

Expand|Select|Wrap|Line Numbers
  1. If Request.Form("somevalue") = True Then
  2. choice = "AND"
  3. Else
  4. choice = "OR"
  5. End If
  6.  
  7. lengthtocut = len(choice) + 1
  8.  
Modify the function slightly:

Expand|Select|Wrap|Line Numbers
  1. Function addparams(fieldname,wordArray,choice)
  2. Dim tmp
  3. tmp = ""
  4.     For each item in wordArray
  5.     tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & choice & " "
  6.     next
  7.     tmp = left(tmp, len(tmp)-lengthtocut) ' Chops off the last "choice "
  8.     addparams = tmp
  9. End Function
Call it with the extra argument

Expand|Select|Wrap|Line Numbers
  1. addparams("field one", myarray, choice)
Gaz
May 21 '09 #12

100+
P: 347
ok ill try that later, i have tried to add a button to the results page for the search to add the mp3id number to a session variable so i can work it like a shopping cart

the code at the beginning of the page is

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3. if InStr(session("recordsInCart"), ","&request.form("recordNum")) = 0 then 
  4.    session("recordsInCart") = session("recordsInCart") + request.form("recordNum") &","
  5. else 
  6.    'do nothing
  7. end if 
  8. %>
  9. <%
  10. Dim strSearch, myarray, strSQL, strSQLExtra 
  11. strSearch = Request.Form("search")
  12.  
  13. 'Loop is now in a function, so that we can feed it each field name. 
  14. Function addparams(fieldname,wordArray)  
  15. Dim tmp 
  16. tmp = "" 
  17.     For each item in wordArray 
  18.     tmp = tmp & " (" & fieldname & " Like '%" & item & "%') AND " 
  19.     next 
  20.     tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 
  21.     addparams = tmp 
  22. End Function 
  23.  
  24. 'Build the word array 
  25. myarray = split(strSearch, " ") 
  26.  
  27. 'Build the SQL  
  28. strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE (" 
  29.  
  30. strSQL = strSQL & addparams("filename", myarray) ' change field one! 
  31.  
  32. strSQL = strSQL & ") OR (" 
  33.  
  34. strSQL = strSQL & addparams("mp3type", myarray) ' change field two! 
  35.  
  36. strSQL = strSQL & ");" 
  37.   %>
  38. <%
  39. Response.Expires=0
  40. Set yaketyConnection=Server.CreateObject("ADODB.Connection")
  41. DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketysearch.mdb") & ";"
  42. yaketyConnection.Open DatabaseDetails
  43. yaketyQuery=strSQL
  44. Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)
  45. %>
the code for the button is in a form

Expand|Select|Wrap|Line Numbers
  1. <%
  2.                                   Response.write("<form action=""searchresult.asp"" method=""post"">" & vbNewline)%>
  3.                                       <TD>
  4.                                     <input type="hidden" name="recordNum" value="<%=yaketyRecordset("idnumbermp3")%>">                                    </TD>
  5.                                       <TD>
  6.                                       <%
  7. if InStr(session("recordsInCart"), ","&yaketyRecordset("idnumbermp3")) > 0 then %>
  8. <INPUT name="submit" type="image" src="../images/makeup/shortlistpinkin.gif" alt="file in shortlist" align="bottom" border="0">
  9. <%else %>
  10. <INPUT name="submit" type="image" src="../images/makeup/shortlistpink.gif" alt="Add to shortlist" align="bottom" border="0">
  11. <%end if 
  12. %></TD>
  13.                                     </form>
the problem is i get an error as follows

Microsoft VBScript runtime error '800a0005'

Invalid procedure call or argument: 'left'

/SEARCH/searchresult.asp, line 23

it still adds the number to the session variable and all works if you go through the pages but when you click the button you have to refresh the page for it to work

if you dont get what i mean its at http://www.yaketyyakallmouth.com/search/search.asp

?????
May 21 '09 #13

100+
P: 347
also about prevoius post i have looked at this and i just dont understand it so i may ahve to leave it, i was just hoping that it would be a case of selecting a radio button on a form and depending on the selection sending the details to a different page depending on it
May 21 '09 #14

GazMathias
Expert 100+
P: 200
I'll try to break it down.

I assumed a check box before, but based on a radio:

Expand|Select|Wrap|Line Numbers
  1. <form>
  2. <input type="radio" name="choice" value="AND" /> All of the words
  3. <br />
  4. <input type="radio" name="choice" value="OR" /> Any of the words
  5. </form>
  6.  
So request("choice") would now contain either "AND" or "OR" :

The WHERE clause to match ALL of the words is (somefield like "%somevalue%") AND (somefield like "%someothervalue%")...etc

To return ANY of the words it is (somefield like "%somevalue%") OR (somefield like "%someothervalue%")...etc

So in essence you need to:
  • Get the choice and the words from the user
  • Pass the choice and the words to the function for it to build the SQL.

You shouldn't need to send the user to a duplicate page where the only difference is one word!
May 21 '09 #15

100+
P: 347
Hi i sort of have it working

here is the code i am using

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim strSearch, myarray, strSQL, strSQLExtra 
  3. strSearch = Request.Form("search")
  4. response.write Request.form("choice")
  5.  
  6. 'Loop is now in a function, so that we can feed it each field name. 
  7. Function addparams(fieldname,wordArray)  
  8. Dim tmp 
  9. tmp = "" 
  10.     For each item in wordArray 
  11.     tmp = tmp & " (" & fieldname & " Like '%" & item & "%') AND " 
  12.     next 
  13.     tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 
  14.     addparams = tmp 
  15. End Function 
  16.  
  17. 'Build the word array 
  18. myarray = split(strSearch, " ") 
  19.  
  20. 'Build the SQL  
  21. strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE (" 
  22.  
  23. strSQL = strSQL & addparams("filename", myarray) ' change field one! 
  24.  
  25. strSQL = strSQL & ")" & Request.form("choice") & " (" 
  26.  
  27. strSQL = strSQL & addparams("mp3type", myarray) ' change field two! 
  28.  
  29. strSQL = strSQL & ");" 
  30.   %>
  31. <%
  32. Response.Expires=0
  33. Set yaketyConnection=Server.CreateObject("ADODB.Connection")
  34. DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketysearch.mdb") & ";"
  35. yaketyConnection.Open DatabaseDetails
  36. yaketyQuery=strSQL
  37. Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)
  38. %>
form

Expand|Select|Wrap|Line Numbers
  1. <form method="POST" action="searchresult.asp">
  2.                                   <br />
  3. <table width="60%" border="0" cellspacing="0" cellpadding="0">
  4.   <tr>
  5.     <td><DIV align="right">All&nbsp;of&nbsp;the&nbsp;words </DIV></td>
  6.     <td><INPUT type="radio" name="choice" id="radio2" value="AND"></td>
  7.   </tr>
  8.   <tr>
  9.     <td><DIV align="right">Any&nbsp;of&nbsp;the&nbsp;words </DIV></td>
  10.     <td><INPUT type="radio" name="choice" id="radio" value="OR"></td>
  11.   </tr>
  12.   <tr>
  13.     <td width="50%"><div align="right" class="style9">SEARCH <SPAN class="style5 style8">*</SPAN></div></td>
  14.     <td width="50%"><input name="search" type="text" id="search" size="40"/></td>
  15.   </tr>
  16.   <tr>
  17.     <td height="10" valign="baseline"><div align="right" class="style4">
  18.       <DIV align="center" class="style5">
  19.         <DIV align="right">*</DIV>
  20.       </DIV>
  21.     </div></td>
  22.     <td height="10" valign="top"><DIV align="left"><SPAN class="style5"><SPAN class="style6">Required</SPAN></SPAN></DIV></td>
  23.   </tr>
  24.  
  25.   <tr>
  26.     <td colspan="2">
  27.  
  28.         <div align="center">
  29.  
  30.           <input type="image" name="imageField" id="imageField" src="../images/makeup/emailbutton.gif">
  31.         </div></td>
  32.   </tr>
  33. </table>
  34. <br /><br />
  35.                                   </form>
works fine with the OR but returns nothing with the AND
May 21 '09 #16

GazMathias
Expert 100+
P: 200
Instead of executing the sql, response.write it out and post it here, might clue us in.
May 21 '09 #17

100+
P: 347
this is the sql for the and

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE ( (filename Like '%female%') AND (filename Like '%african%') AND (filename Like '%accent%') ) AND ( (mp3type Like '%female%') AND (mp3type Like '%african%') AND (mp3type Like '%accent%') ); 
and the or

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE ( (filename Like '%female%') AND (filename Like '%african%') AND (filename Like '%accent%') ) OR ( (mp3type Like '%female%') AND (mp3type Like '%african%') AND (mp3type Like '%accent%') ); 
May 21 '09 #18

GazMathias
Expert 100+
P: 200
This line should ALWAYS have "OR" in it.

Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & ") OR ("
  2.  
Not

Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & ")" & Request.form("choice") & " ("
  2.  
Otherwise you are telling it to find all of the words in both fields.


So what we are saying is:

Find all of the words in filename OR mp3type

Find any of the words in filename OR mp3type

Gaz.
May 21 '09 #19

100+
P: 347
sorry i thought this was where the coice from the form would go
May 21 '09 #20

100+
P: 347
sorted thanks, do you have any idea about the problem about 7 posts back when i get this error

Microsoft VBScript runtime error '800a0005'

Invalid procedure call or argument: 'left'

/SEARCH/searchresult.asp, line 23
May 21 '09 #21

GazMathias
Expert 100+
P: 200
Hi,

Seeing line 23 (and any related data) may help with this but I feel that you need to learn to debug your code. Seeing a variable before and after it is processed helps somewhat if you think about it logically but often times its not enough.

One problem coding ASP is that when you push data through a function or sub, you cannot see what the procedure is actually doing to the data, so what I tend to do in these scenarios is copy the code over to a standard module in either Excel or Access (changing some parts to suit, where necessary) and add a break point, then use the Immediate Window to call the procedure directly and then step through the code using F8, that way you can see what happens at each line in the code and understand exactly where the problem occurs (or occasionally realise that what you've written doesn't actually do what you want!).

That may be a lot to take in, but learning to debug code is an essential asset.

Gaz.
May 22 '09 #22

100+
P: 347
sorry thought i included the code line here it is

Expand|Select|Wrap|Line Numbers
  1.     tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 
its part of this set of code

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim strSearch, myarray, strSQL, strSQLExtra 
  3. strSearch = Request.Form("search")
  4.  
  5. 'Loop is now in a function, so that we can feed it each field name. 
  6. Function addparams(fieldname,wordArray)  
  7. Dim tmp 
  8. tmp = "" 
  9.     For each item in wordArray 
  10.     tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & Request.form("choice") & " " 
  11.     next 
  12.     tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 
  13.     addparams = tmp 
  14. End Function 
  15.  
  16. 'Build the word array 
  17. myarray = split(strSearch, " ") 
  18.  
  19. 'Build the SQL  
  20. strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE (" 
  21.  
  22. strSQL = strSQL & addparams("filename", myarray) ' change field one! 
  23.  
  24. strSQL = strSQL & ") OR (" 
  25.  
  26. strSQL = strSQL & addparams("mp3type", myarray) ' change field two! 
  27.  
  28. strSQL = strSQL & ");" 
  29.  
  30.   %>
as for testing the code i have no idea how to do that in the way you said
May 26 '09 #23

GazMathias
Expert 100+
P: 200
My guess is because the function is processing nothing, so not building tmp.

You can replicate this error like this:

Expand|Select|Wrap|Line Numbers
  1. testvar = ""
  2. tmp = left(testvar,len(tmp)-4)
  3.  
So you need to figure out why nothing is being passed to the function.

The problem is either with strSearch or myArray
May 26 '09 #24

100+
P: 347
could it be something to do with the button pointing the form back to the same page and the page is not getting the information from the choice and search boxes? If it is do you know how i can get round this
May 26 '09 #25

GazMathias
Expert 100+
P: 200
That would be a good candidate, yes.

You could pass the search data to session variables and use those to search instead.

This bit at the top changes:

Expand|Select|Wrap|Line Numbers
  1. strSearch = Request.Form("search")
To

Expand|Select|Wrap|Line Numbers
  1. If Not Request.Form("search") = "" Then
  2. session("search") = Request.Form("search")
  3. session("choice") = Request.Form("choice")
  4. End If
So does this:

Expand|Select|Wrap|Line Numbers
  1. myarray = split(strSearch, " ") 
To

Expand|Select|Wrap|Line Numbers
  1. myarray = split(session("search"), " ")
And in the function

Expand|Select|Wrap|Line Numbers
  1. tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & Request.form("choice") & " " 
changes to:

Expand|Select|Wrap|Line Numbers
  1. tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & session("choice") & " "
So if we arrive here from the search form, we pass the search data to the session, or else we use what's already in the session from the previous search. We also obviously now use session data as the source instead of the query string.

Does that help?
May 26 '09 #26

100+
P: 347
yes that works great thanks a lot
May 26 '09 #27

Post your reply

Sign in to post your reply or Sign up for a free account.