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]

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

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.as p, 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 "%someothervalu e%")...etc

To return ANY of the words it is (somefield like "%somevalue %") OR (somefield like "%someothervalu e%")...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!