...

View Full Version : ASP - MySQL - Search!!!!



nathan123
09-30-2005, 01:04 PM
Here is some ASP code in my page:


<%

Dim strURL



Dim cnnSearch
Dim rstSearch
Dim strDBPath

Dim strSQL
Dim strSearch


strURL = Request.ServerVariables("URL")


strSearch = Request.QueryString("search")
'strSearch = Replace(strSearch, "'", "''")
%>


<form action="<%= strURL %>" method="get" class="style52">
<input name="search" value="<%= strSearch %>" />
<input type=submit style="border:1px solid #808080; WIDTH: 150px; BACKGROUND-COLOR: #f3f3f3; font-size:11px; font-family:Verdana" accesskey=s value=" Search Your Results">
</form>
<%
If strSearch <> "" Then

Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=; DATABASE=; UID=;PASSWORD=; OPTION="


strPERSON= Session("User")
strSQL = "SELECT *" _
& "FROM TABLE " _
& "WHERE PERSON = " & "'" & Session("User") & "'"_
& "AND FIELD1 LIKE '%" & Replace(strSearch, "'", "''") & "%' "_
& "AND FIELD2 LIKE '%" & Replace(strSearch, "'", "''") & "%' "_
& "AND FIELD3 LIKE '%" & Replace(strSearch, "'", "''") & "%' "_
& "AND FIELD4 LIKE '%" & Replace(strSearch, "'", "''") & "%' "



Set rstSearch = cnnSearch.Execute(strSQL)

%>
<table border="1" align="center">
<tr>
<th><span class="style55">FIELD1</span></th>
<th><span class="style52">FIELD2</span></th>
<th><span class="style55">FIELD3</span></th>
<th><span class="style55">FIELD4</span></th>
<th><span class="style55">FIELD5</span></th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><span class="style52"> <%= rstSearch.Fields("FIELD1").Value %></span></td>
<td><span class="style52"><%= rstSearch.Fields("FIELD2").Value %></span></td>
<td><span class="style52"><%= rstSearch.Fields("FIELD3").Value %></span></td>
<td><span class="style52"><%= rstSearch.Fields("FIELD4").Value %></span></td>
<td><span class="style52"><%= rstSearch.Fields("FIELD5").Value %></span></td>
</tr>
<%
rstSearch.MoveNext
Loop
%>
</table>
<%
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>

This works fine, but only searches the first field... i want it to select the others... or can i use check boxes so the user can select which field they want to search??

Please help.
Nathan

hinch
09-30-2005, 04:16 PM
its a long shot since its been a while since i've tried mysql and asp together but try putting () around each segment of the where clause

where (user=session) AND (field1 like request()) AND (etc)


but surely if its a search it should be or's not and's ?

heres one of my dodgy search strings from something i did a while back (yes i know its slack before anyone jumps at me)


sql = "SELECT Title, DatePosted, 'News' as SID FROM tblPressRelease WHERE (Title LIKE '%"&EscapeSpecial(Request.Form("Search"))&"%') OR (BodyCopy LIKE '%"&EscapeSpecial(Request.Form("Search"))&"%') " &_
"UNION ALL " &_
"SELECT ExpoName, DatesofShow, 'Events' as SID FROM tblevents WHERE ExpoName LIKE '%"&EscapeSpecial(Request.Form("Search"))&"%' " &_
"UNION ALL " &_
"SELECT CustomerName, IndSector, 'Customers' as SID FROM tblCustomers WHERE CustomerName LIKE '%"&EscapeSpecial(Request.Form("Search"))&"%' " &_
"UNION ALL " &_
"SELECT PartnerName, Details, 'Partners' as SID FROM tblPartners WHERE (PartnerName LIKE '%"&EscapeSpecial(Request.Form("Search"))&"%') OR (Details LIKE '%"&EscapeSpecial(Request.Form("Search"))&"%') "

TheShaner
09-30-2005, 09:23 PM
Adding checkboxes to specify which field to search in is also an idea. Just depends on how you want them to be able to search. The way you have your query set up is that if one field fails, the whole search fails. Also, if you switch them to ORs, it'll fail if you leave one field empty.

-Shane



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum