Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Sep 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ASP - MySQL - Search!!!!

    Here is some ASP code in my page:

    Code:
    <%
    
    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

  • #2
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    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)

    Code:
    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"))&"%') "

  • #3
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •