SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Wilmington, NC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching with multiple criteria

    I have built a search page in asp so that a user can search with multiple criterias. For example, they can search for houses by the number of bedrooms and by the price. I used dynamic dropdown menus for the criteria.

    BUT what if the user wants to search by price range but not by bedrooms? How do I code such that the bedrooms dropdown is "ignored"? I'm thinking I must add another variable to the dropdown menu so that the user has the choice of saying "any number", but what do I set this variable equal to?

    Thanks!

  2. #2
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you post the code for the page, i can have a look at it and make a suggestion

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Wilmington, NC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, DHTMLGOD, for offering to look at this.

    The two choices in this example are MLS area (a region mapping method used in real estate) and Bedrooms. As you will see below, I will later include choices for price range, square footage, and bathrooms, but I want to get these two options figured out first!

    <%@LANGUAGE="VBSCRIPT"%>
    <!--#include file="Connections/connProperties.asp" -->
    <%
    set rsMLS = Server.CreateObject("ADODB.Recordset")
    rsMLS.ActiveConnection = MM_connProperties_STRING
    rsMLS.Source = "SELECT MLSArea FROM PropertiesFull"
    rsMLS.CursorType = 0
    rsMLS.CursorLocation = 2
    rsMLS.LockType = 3
    rsMLS.Open()
    rsMLS_numRows = 0
    %>
    <%
    set rsBed = Server.CreateObject("ADODB.Recordset")
    rsBed.ActiveConnection = MM_connProperties_STRING
    rsBed.Source = "SELECT DISTINCT Bedrooms FROM PropertiesFull"
    rsBed.CursorType = 0
    rsBed.CursorLocation = 2
    rsBed.LockType = 3
    rsBed.Open()
    rsBed_numRows = 0
    %>
    <html>
    <head>
    <title>Real Estate - Search Properties</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>
    <body bgcolor="#FFFFFF" text="#000000">
    <p>&nbsp;</p>
    <form name="frmSearch" method="get" action="results.asp">
    <table width="95%" border="0" cellspacing="0" cellpadding="2">
    <tr>
    <td width="32%">
    <div align="right">Price Range</div>
    </td>
    <td width="68%">&nbsp;</td>
    </tr>
    <tr>
    <td width="32%">
    <div align="right">MLS Area</div>
    </td>
    <td width="68%">
    <select name="mnuMLS">
    <%
    While (NOT rsMLS.EOF)
    %>
    <option value="<%=(rsMLS.Fields.Item("MLSArea").Value)%>" ><%=(rsMLS.Fields.Item("MLSArea").Value)%></option>
    <%
    rsMLS.MoveNext()
    Wend
    If (rsMLS.CursorType > 0) Then
    rsMLS.MoveFirst
    Else
    rsMLS.Requery
    End If
    %>
    </select>
    </td>
    </tr>
    <tr>
    <td width="32%">
    <div align="right">Square Footage</div>
    </td>
    <td width="68%">&nbsp;</td>
    </tr>
    <tr>
    <td width="32%">
    <div align="right">Bedrooms</div>
    </td>
    <td width="68%">
    <select name="mnuBed">
    <%
    While (NOT rsBed.EOF)
    %>
    <option value="<%=(rsBed.Fields.Item("Bedrooms").Value)%>"><%=(rsBed.Fields.Item("Bedrooms").Value)%></option>
    <%
    rsBed.MoveNext()
    Wend
    If (rsBed.CursorType > 0) Then
    rsBed.MoveFirst
    Else
    rsBed.Requery
    End If
    %>
    </select>
    </td>
    </tr>
    <tr>
    <td width="32%">
    <div align="right">Bathrooms</div>
    </td>
    <td width="68%">&nbsp; </td>
    </tr>
    </table>
    <p>&nbsp;</p>
    <p>&nbsp;</p>
    <p>
    <input type="submit" name="optSearch" value="Find My Dream Homes">
    </p>
    </form>
    <p>&nbsp;</p>
    </body>
    </html>
    <%
    rsMLS.Close()
    %>
    <%
    rsBed.Close()
    %>

    Once again, thank you so much for helping me!

  4. #4
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for posting hte code, im at work right now, and i dont have time to look at it right now, but i will when i get home

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Wilmington, NC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much, I truly appreciate your input!

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    I can try to help you out now if you'd like.


    You've got two options:
    1) You can add a value of zero to your tables for "ANY"
    2) You can add code specific which tells it to ignore it.

    For the code to ignore it,
    Right before your while not *.EOF lines, add an option line which says something like this:
    <option value="any">Any</option>

    Then in the page which reads these options, have code something like this:

    Code:
    <%
    dim mnuMLS, mnuBed, strSQL, WhereSQL
    dim strStr, strInt
    strStr = "string"
    strInt = "integer"
    mnuMls = Request.Form("mnuMLS")
    mnuBed = Request.Form("mnuBed")
    WhereSQL = ""
    if mnuMLS <> "any" and mnuMLS <> "" then
       WhereSQL = BuildWhere("", "mnuMLS", strStr, mnuMLS)
    end if
    if mnuBed <> "any" and mnuBed <> "" then
       WhereSQL = BuildWhere("", "mnuMLS", strInt, mnuMLS)
    end if
    StrSQL = "SELECT * FROM TABLENAME" & WhereSQL
    
    function BuildWhere(IncomingSQL, FieldName, FieldType, FieldValue)
       dim strSQL
       if IncomingSQL = "" then
          strSQL = " WHERE "
       else
          strSQL = IncomingSQL & " AND "
       end if 
       strSQL = strSQL & FieldName & " = "
       if FieldType = "string" then
           strSQL = strSQL & "'" & FieldValue & "'"
       else
           strSQL = strSQL & "'" & FieldValue & "'"
       end if
       BuildWhere = strSQL
    end function
    %>
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Wilmington, NC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like the "Any" option better, it is more likely what users will want to see available.

    I tried including it as another value in the dropdown menu. I set the Item Label as All and the Value as %. However, the following error resulted when I tested the page and chose All from the MLS Area menu and 3 from the Bedrooms menu:

    Syntax error in query expression 'MLSArea = % AND Bedrooms = 3'.

    What have I done wrong?

  8. #8
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by Seabelle
    I like the "Any" option better, it is more likely what users will want to see available.

    I tried including it as another value in the dropdown menu. I set the Item Label as All and the Value as %. However, the following error resulted when I tested the page and chose All from the MLS Area menu and 3 from the Bedrooms menu:

    Syntax error in query expression 'MLSArea = % AND Bedrooms = 3'.

    What have I done wrong?
    I'm assuming MLSArea is a string? If so, then use '%' when building the query expression.

    If you don't want a specific value searched for, you are better off not searching on the field at all. The resulting query will be MUCH faster. Just something to think about.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style


Bookmarks

Posting Permissions

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