Search function

[colinod]

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????

[GazMathias]

Hi,

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

For example:

Dim strSearch, myarray, strSQL, strSQLExtra
strSearch =  "some words to search for"
 
myarray = split(strSearch, " ")
 
strSQL = "SELECT STUFF FROM TABLE WHERE "
 
For each item in myarray
strSQLExtra = strSQLExtra & " (SomeField Like '*" & item & "*') OR "
next
 
strSQLExtra = left(strSQLExtra, len(strSQLExtra)-3) ' Chops off the last "OR "
 
strSQL = strSQL & strSQLExtra
 
Response.Write strSQL
 

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.

[colinod]

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

<%
Response.Expires=0
Set Connection=Server.CreateObject("ADODB.Connection")
DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/database.mdb") & ";"
.Open DatabaseDetails
Query="SELECT * FROM celebs  where idnumber =" &actorid& " order by orderno"
Set Recordset=Connection.execute(Query)
%>

just cant figure out how to use the generated sql in this

[GazMathias]

@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

[colinod]

sorry code should have read

<% 
Response.Expires=0 
Set Connection=Server.CreateObject("ADODB.Connection") 
DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/database.mdb") & ";" 
.Open DatabaseDetails 
Query="SELECT * FROM mp3  where idnumber =" &mp3id& " order by orderno" 
Set Recordset=Connection.execute(Query) 
%> 

im just not sure how to get the sql from your code example intothe sql above

[GazMathias]

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!

[colinod]

ok its just me i have it wroking to search 1 field with the following

<%If Len(Request.Form) > 0 Then
    term = Trim(Request.Form("search"))
    term = Replace(term,"'","''")
 
    If InStr(term," ") > 0 Then
        term = Split(term," ")
 
        For x=0 to Ubound(term)
            If x > 0 Then
                clause = clause & " AND"
            End If
        clause = clause & " filename LIKE '%" & term(x) & "%'"
        Next
    Else
        clause = " filename LIKE '%" & term & "%'"
    End If
    'sql = " WHERE " & clause
sql = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE" & clause
'Response.Write sql
End If 
%>
<%
Response.Expires=0
Set yaketyConnection=Server.CreateObject("ADODB.Connection")
DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketynew.mdb") & ";"
yaketyConnection.Open DatabaseDetails
yaketyQuery=sql
Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)
%>

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?

[GazMathias]

To search in more than one field:

WHERE ((field 1 like "somevalue") OR (field 2 like "somevalue"))

Gaz.

[colinod]

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

[GazMathias]

You need to combine the two principles, something like:

Dim strSearch, myarray, strSQL, strSQLExtra
strSearch =  "female african accent"
 
'Loop is now in a function, so that we can feed it each field name.
Function addparams(fieldname,wordArray) 
Dim tmp
tmp = ""
    For each item in wordArray
    tmp = tmp & " (" & fieldname & " Like '%" & item & "%') AND "
    next
    tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND "
    addparams = tmp
End Function
 
'Build the word array
myarray = split(strSearch, " ")
 
Build the SQL 
strSQL = "SELECT STUFF FROM TABLE WHERE ("
 
strSQL = strSQL & addparams("field one", myarray) ' change field one!
 
strSQL = strSQL & ") OR ("
 
strSQL = strSQL & addparams("field two", myarray) ' change field two!
 
strSQL = strSQL & ");"
 

Not tested that on a real database but the SQL looks OK.

Gaz

[colinod]

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

[GazMathias]

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:

If Request.Form("somevalue") = True Then
choice = "AND"
Else
choice = "OR"
End If
 
lengthtocut = len(choice) + 1
 

Modify the function slightly:

Function addparams(fieldname,wordArray,choice)
Dim tmp
tmp = ""
    For each item in wordArray
    tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & choice & " "
    next
    tmp = left(tmp, len(tmp)-lengthtocut) ' Chops off the last "choice "
    addparams = tmp
End Function

Call it with the extra argument

addparams("field one", myarray, choice)

Gaz

[colinod]

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

<%
 
if InStr(session("recordsInCart"), ","&request.form("recordNum")) = 0 then 
   session("recordsInCart") = session("recordsInCart") + request.form("recordNum") &","
else 
   'do nothing
end if 
%>
<%
Dim strSearch, myarray, strSQL, strSQLExtra 
strSearch = Request.Form("search")
 
'Loop is now in a function, so that we can feed it each field name. 
Function addparams(fieldname,wordArray)  
Dim tmp 
tmp = "" 
    For each item in wordArray 
    tmp = tmp & " (" & fieldname & " Like '%" & item & "%') AND " 
    next 
    tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 
    addparams = tmp 
End Function 
 
'Build the word array 
myarray = split(strSearch, " ") 
 
'Build the SQL  
strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE (" 
 
strSQL = strSQL & addparams("filename", myarray) ' change field one! 
 
strSQL = strSQL & ") OR (" 
 
strSQL = strSQL & addparams("mp3type", myarray) ' change field two! 
 
strSQL = strSQL & ");" 
  %>
<%
Response.Expires=0
Set yaketyConnection=Server.CreateObject("ADODB.Connection")
DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketysearch.mdb") & ";"
yaketyConnection.Open DatabaseDetails
yaketyQuery=strSQL
Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)
%>

the code for the button is in a form

<%
                                  Response.write("<form action=""searchresult.asp"" method=""post"">" & vbNewline)%>
                                      <TD>
                                    <input type="hidden" name="recordNum" value="<%=yaketyRecordset("idnumbermp3")%>">                                    </TD>
                                      <TD>
                                      <%
if InStr(session("recordsInCart"), ","&yaketyRecordset("idnumbermp3")) > 0 then %>
<INPUT name="submit" type="image" src="../images/makeup/shortlistpinkin.gif" alt="file in shortlist" align="bottom" border="0">
<%else %>
<INPUT name="submit" type="image" src="../images/makeup/shortlistpink.gif" alt="Add to shortlist" align="bottom" border="0">
<%end if 
%></TD>
                                    </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

?????

[colinod]

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

[GazMathias]

I'll try to break it down.

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

<form>
<input type="radio" name="choice" value="AND" /> All of the words
<br />
<input type="radio" name="choice" value="OR" /> Any of the words
</form>
 

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!

[colinod]

Hi i sort of have it working

here is the code i am using

<%
Dim strSearch, myarray, strSQL, strSQLExtra 
strSearch = Request.Form("search")
response.write Request.form("choice")
 
'Loop is now in a function, so that we can feed it each field name. 
Function addparams(fieldname,wordArray)  
Dim tmp 
tmp = "" 
    For each item in wordArray 
    tmp = tmp & " (" & fieldname & " Like '%" & item & "%') AND " 
    next 
    tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 
    addparams = tmp 
End Function 
 
'Build the word array 
myarray = split(strSearch, " ") 
 
'Build the SQL  
strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE (" 
 
strSQL = strSQL & addparams("filename", myarray) ' change field one! 
 
strSQL = strSQL & ")" & Request.form("choice") & " (" 
 
strSQL = strSQL & addparams("mp3type", myarray) ' change field two! 
 
strSQL = strSQL & ");" 
  %>
<%
Response.Expires=0
Set yaketyConnection=Server.CreateObject("ADODB.Connection")
DatabaseDetails="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("../../private/yaketysearch.mdb") & ";"
yaketyConnection.Open DatabaseDetails
yaketyQuery=strSQL
Set yaketyRecordset=yaketyConnection.execute(yaketyQuery)
%>

form

<form method="POST" action="searchresult.asp">
                                  <br />
<table width="60%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td><DIV align="right">All&nbsp;of&nbsp;the&nbsp;words </DIV></td>
    <td><INPUT type="radio" name="choice" id="radio2" value="AND"></td>
  </tr>
  <tr>
    <td><DIV align="right">Any&nbsp;of&nbsp;the&nbsp;words </DIV></td>
    <td><INPUT type="radio" name="choice" id="radio" value="OR"></td>
  </tr>
  <tr>
    <td width="50%"><div align="right" class="style9">SEARCH <SPAN class="style5 style8">*</SPAN></div></td>
    <td width="50%"><input name="search" type="text" id="search" size="40"/></td>
  </tr>
  <tr>
    <td height="10" valign="baseline"><div align="right" class="style4">
      <DIV align="center" class="style5">
        <DIV align="right">*</DIV>
      </DIV>
    </div></td>
    <td height="10" valign="top"><DIV align="left"><SPAN class="style5"><SPAN class="style6">Required</SPAN></SPAN></DIV></td>
  </tr>
 
  <tr>
    <td colspan="2">
 
        <div align="center">
 
          <input type="image" name="imageField" id="imageField" src="../images/makeup/emailbutton.gif">
        </div></td>
  </tr>
</table>
<br /><br />
                                  </form>

works fine with the OR but returns nothing with the AND

[GazMathias]

Instead of executing the sql, response.write it out and post it here, might clue us in.

[colinod]

this is the sql for the and

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

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%') ); 

[GazMathias]

This line should ALWAYS have "OR" in it.

strSQL = strSQL & ") OR ("
 

Not

strSQL = strSQL & ")" & Request.form("choice") & " ("
 

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.

[colinod]

sorry i thought this was where the coice from the form would go

[colinod]

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

[GazMathias]

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.

[colinod]

sorry thought i included the code line here it is

    tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 

its part of this set of code

<%
Dim strSearch, myarray, strSQL, strSQLExtra 
strSearch = Request.Form("search")
 
'Loop is now in a function, so that we can feed it each field name. 
Function addparams(fieldname,wordArray)  
Dim tmp 
tmp = "" 
    For each item in wordArray 
    tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & Request.form("choice") & " " 
    next 
    tmp = left(tmp, len(tmp)-4) ' Chops off the last "AND " 
    addparams = tmp 
End Function 
 
'Build the word array 
myarray = split(strSearch, " ") 
 
'Build the SQL  
strSQL = "SELECT * FROM mp3 INNER JOIN celebs ON mp3.celebid = celebs.idnumber WHERE (" 
 
strSQL = strSQL & addparams("filename", myarray) ' change field one! 
 
strSQL = strSQL & ") OR (" 
 
strSQL = strSQL & addparams("mp3type", myarray) ' change field two! 
 
strSQL = strSQL & ");" 
 
  %>

as for testing the code i have no idea how to do that in the way you said

[GazMathias]

My guess is because the function is processing nothing, so not building tmp.

You can replicate this error like this:

testvar = ""
tmp = left(testvar,len(tmp)-4)
 

So you need to figure out why nothing is being passed to the function.

The problem is either with strSearch or myArray

[colinod]

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

[GazMathias]

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:

strSearch = Request.Form("search")

To

If Not Request.Form("search") = "" Then
session("search") = Request.Form("search")
session("choice") = Request.Form("choice")
End If

So does this:

myarray = split(strSearch, " ") 

To

myarray = split(session("search"), " ")

And in the function

tmp = tmp & " (" & fieldname & " Like '%" & item & "%') " & Request.form("choice") & " " 

changes to:

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?

[colinod]

yes that works great thanks a lot