Created
September 17, 2009 22:49
-
-
Save neilbradley/188770 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!--#include file="../includes/site-variables.asp" --> | |
| <!--#include file="../Connections/dbconn.asp" --> | |
| <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" | |
| "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> | |
| <html> | |
| <head> | |
| <title>Search</title> | |
| </head> | |
| <body> | |
| <div class="search"> | |
| <h3>Search form</h3> | |
| <form id="form1" name="form1" method="post" action="gist188770.asp"> | |
| <label>Street: <input type="text" name="searchStreet" value="<%=Server.HtmlEncode(Request("searchStreet") & "") %>" /></label> | |
| <label>Town: <input type="text" name="searchTown" value="<%=Server.HtmlEncode(Request("searchTown") & "") %>" /></label> | |
| <input type="submit" name="Submit" value="Submit" /> | |
| </form> | |
| </div> | |
| <% | |
| if (Request.ServerVariables("REQUEST_METHOD") = "POST") then | |
| 'arrived via post get form values and do search | |
| Dim myRecordSet | |
| Dim myRecordSet_numRows | |
| Set myRecordSet = Server.CreateObject("ADODB.Recordset") | |
| myRecordSet.ActiveConnection = MM_dbconn_STRING | |
| 'collect the form input | |
| set objDBParam = objDBCommand.CreateParameter("@ContentStreet",200,1,100) | |
| objDBCommand.Parameters.Append objDBParam | |
| objDBCommand.Parameters("@ContentStreet") = Request.QueryString("searchStreet") | |
| set objDBParam = Nothing | |
| set objDBParam = objDBCommand.CreateParameter("@ContentStreet",200,1,100) | |
| objDBCommand.Parameters.Append objDBParam | |
| objDBCommand.Parameters("@ContentTown") = Request.QueryString("searchTown") | |
| set objDBParam = Nothing | |
| set objDBParam = objDBCommand.CreateParameter("@ContentStreet",200,1,20) | |
| objDBCommand.Parameters.Append objDBParam | |
| objDBCommand.Parameters("@ContentPostcode") = Request.QueryString("searchPostcode") | |
| set objDBParam = Nothing | |
| 'check for a match | |
| myRecordSet.Source = "SELECT *" | |
| myRecordSet.Source = myRecordSet.Source& "FROM (" | |
| myRecordSet.Source = myRecordSet.Source& "SELECT id" | |
| myRecordSet.Source = myRecordSet.Source& "FROM (" | |
| myRecordSet.Source = myRecordSet.Source& "SELECT id" | |
| myRecordSet.Source = myRecordSet.Source& "FROM VWTenantPropertiesResults" | |
| myRecordSet.Source = myRecordSet.Source& "WHERE ContentStreet LIKE '%" & "@ContentStreet" & "%'" | |
| myRecordSet.Source = myRecordSet.Source& "UNION ALL" | |
| myRecordSet.Source = myRecordSet.Source& "SELECT id" | |
| myRecordSet.Source = myRecordSet.Source& "FROM VWTenantPropertiesResults" | |
| myRecordSet.Source = myRecordSet.Source& "WHERE ContentTown LIKE '%" & "@ContentTown" & "%'" | |
| myRecordSet.Source = myRecordSet.Source& "UNION ALL" | |
| myRecordSet.Source = myRecordSet.Source& "SELECT id" | |
| myRecordSet.Source = myRecordSet.Source& "FROM VWTenantPropertiesResults" | |
| myRecordSet.Source = myRecordSet.Source& "WHERE ContentPostCode LIKE '%" & "@ContentPostcode" & "%'" | |
| myRecordSet.Source = myRecordSet.Source& ") qi" | |
| myRecordSet.Source = myRecordSet.Source& "GROUP BY" | |
| myRecordSet.Source = myRecordSet.Source& "id" | |
| myRecordSet.Source = myRecordSet.Source& "HAVING COUNT(*) >= 2" | |
| myRecordSet.Source = myRecordSet.Source& ") q" | |
| myRecordSet.Source = myRecordSet.Source& "JOIN VWTenantPropertiesResults r" | |
| myRecordSet.Source = myRecordSet.Source& "ON r.id = q.id" | |
| myRecordSet.Source = myRecordSet.Source& "WHERE ContentBedrooms BETWEEN 1 AND 4" | |
| myRecordSet.Source = myRecordSet.Source& "AND ContentPrice BETWEEN 50 AND 500" | |
| myRecordSet.Source = myRecordSet.Source& "ORDER BY" | |
| myRecordSet.Source = myRecordSet.Source& "ContentPrice" | |
| 'display the results | |
| if myRecordSet.BOF then | |
| response.write("Latest properties:<br>") | |
| do until myRecordSet.EOF | |
| %> | |
| <div class='result'>") | |
| <dl><%=myRecordSet("ContentTitle")%></dl> | |
| <dt><%=myRecordSet("ContentStreet")%></dt> | |
| <dt><%=myRecordSet("ContentTown")%></dt> | |
| <dt><%=myRecordSet("ContentPostcode")%></dt> | |
| </div><% | |
| myRecordSet.MoveNext | |
| loop | |
| end if | |
| else | |
| 'arrived via get show last 10 results | |
| Dim myRecordSet2 | |
| Dim myRecordSet2_numRows | |
| Set myRecordSet2 = Server.CreateObject("ADODB.Recordset") | |
| myRecordSet2.ActiveConnection = MM_dbconn_STRING | |
| myRecordSet2.Source = "SELECT TOP 10 FROM VWTenantPropertiesResults ORDER BY ContentPrice" | |
| 'display the results | |
| if myRecordSet2.BOF then | |
| do until myRecordSet2.EOF | |
| %> | |
| <div class='result'>") | |
| <dl><%=myRecordSet2("ContentTitle")%></dl> | |
| <dt><%=myRecordSet2("ContentStreet")%></dt> | |
| <dt><%=myRecordSet2("ContentTown")%></dt> | |
| <dt><%=myRecordSet2("ContentPostcode")%></dt> | |
| </div><% | |
| myRecordSet2.MoveNext | |
| loop | |
| end if | |
| end if | |
| %> | |
| </body> | |
| </html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment