January 20, 201115 yr Hi there, I am currently working on a website for an estate agent. On their search they want the user to input a Minimum Price, Maximum Price and specify the minimum amount of bedrooms required. The table in the MS SQL database for bedrooms is called beds and I have it set up as bedsid bedsname and then ID's from 1 to 10 for the amount of bedrooms. I then have this as the search form: <form action="search_results.asp" method="get" name="form1" class="text_center" id="form1" onsubmit="MM_validateForm('maxprice','','NisNum','minprice','','NisNum','beds');return document.MM_returnValue"> <input type="hidden" name="section22" value="1" /> <p> <label for="name"><strong>Min Price</strong></label> <select name="minprice" id="minprice"> <option value="0" selected="selected">£0</option> <option value="100000">£100,000</option> <option value="125000">£125,000</option> <option value="150000">£150,000</option> <option value="175000">£175,000</option> <option value="200000">£200,000</option> <option value="225000">£225,000</option> <option value="250000">£250,000</option> <option value="275000">£275,000</option> <option value="300000">£300,000</option> <option value="325000">£325,000</option> <option value="350000">£350,000</option> <option value="375000">£375,000</option> <option value="400000">£400,000</option> <option value="425000">£425,000</option> <option value="450000">£450,000</option> <option value="475000">£475,000</option> <option value="500000">£500,000</option> <option value="550000">£550,000</option> <option value="600000">£600,000</option> <option value="650000">£650,000</option> <option value="700000">£700,000</option> <option value="800000">£800,000</option> <option value="900000">£900,000</option> <option value="1000000">£1,000,000</option> <option value="1250000">£1,250,000</option> <option value="1500000">£1,500,000</option> <option value="2000000">£2,000,000</option> <option value="2000000">£3,000,000</option> </select> </p> <p> <label for="maxprice"><strong>Max Price</strong></label> <select name="maxprice" id="maxprice"> <option value="100000">£100,000</option> <option value="125000">£125,000</option> <option value="150000">£150,000</option> <option value="175000">£175,000</option> <option value="200000">£200,000</option> <option value="225000">£225,000</option> <option value="250000">£250,000</option> <option value="275000">£275,000</option> <option value="300000">£300,000</option> <option value="325000">£325,000</option> <option value="350000">£350,000</option> <option value="375000">£375,000</option> <option value="400000">£400,000</option> <option value="425000">£425,000</option> <option value="450000">£450,000</option> <option value="475000">£475,000</option> <option value="500000">£500,000</option> <option value="550000">£550,000</option> <option value="600000">£600,000</option> <option value="650000">£650,000</option> <option value="700000">£700,000</option> <option value="800000">£800,000</option> <option value="900000">£900,000</option> <option value="1000000" selected="selected">£1,000,000</option> <option value="1250000">£1,250,000</option> <option value="1500000">£1,500,000</option> </select> </p> <p><strong>Bedrooms</strong> <select name="beds" id="beds"> <option value="1">1 Bedroom</option> <option value="2">2 Bedrooms</option> <option value="3" selected="selected">3 Bedrooms</option> <option value="4">4 Bedrooms</option> <option value="5">5 Bedrooms</option> <option value="6">6 Bedrooms</option> <option value="7">7 Bedrooms</option> </select> </p> <p class="text_center"> <input name="Submit" type="image" class="imageborder2" value="Search" src="images/search.png" alt="Search" title="Search" /> <input type="hidden" name="MM_insert" value="true" /> </p> </form> The code at the top of the results page looks like this: <% Dim rs__varsection rs__varsection = "1" if (request("section") <> "") then rs__varsection = request("section") %> <% Dim rs__varbeds rs__varbeds = "%" if (request("beds") <> "") then rs__varbeds = request("beds") %> <% Dim rs__varstatus rs__varstatus = "%" if (request("status") <> "") then rs__varstatus = request("status") %> <% Dim rs__varpl rs__varpl = "0" if (Request.QueryString("minprice") <> "") then rs__varpl = Request.QueryString("minprice") %> <% Dim rs__varpu rs__varpu = "99999999999999" if (Request.QueryString("maxprice") <> "") then rs__varpu = Request.QueryString("maxprice") %> <% Dim rs__varb rs__varb = "10" if (Request.QueryString("beds") <> "") then rs__varb = Request.QueryString("beds") %> <% set rs = Server.CreateObject("ADODB.Recordset") rs.ActiveConnection = Conn rs.Source = "SELECT * FROM prop, section, beds, status WHERE beds = bedsid AND status = statusid AND section = sectionid AND section like '" + Replace(rs__varsection, "'", "''") + "' AND beds like '" + Replace(rs__varbeds, "'", "''") + "' AND status like '" + Replace(rs__varstatus, "'", "''") + "' AND price between " + Replace(rs__varpl, "'", "''") + " and " + Replace(rs__varpu, "'", "''") + " ORDER BY price DESC" rs.CursorType = 0 rs.CursorLocation = 2 rs.LockType = 3 rs.Open() rs_numRows = 0 %> <% Dim rs2__MMColParam rs2__MMColParam = "1" if (Request.QueryString("statusid") <> "") then rs2__MMColParam = Request.QueryString("statusid") %> <% set rs2 = Server.CreateObject("ADODB.Recordset") rs2.ActiveConnection = Conn rs2.Source = "SELECT * FROM status WHERE statusid = " + Replace(rs2__MMColParam, "'", "''") + "" rs2.CursorType = 0 rs2.CursorLocation = 2 rs2.LockType = 3 rs2.Open() rs2_numRows = 0 %> <% Dim rs3__MMColParam rs3__MMColParam = "1" if (Request.QueryString("bedsid") <> "") then rs3__MMColParam = Request.QueryString("bedsid") %> <% set rs3 = Server.CreateObject("ADODB.Recordset") rs3.ActiveConnection = Conn rs3.Source = "SELECT * FROM beds WHERE bedsid = " + Replace(rs3__MMColParam, "'", "''") + "" rs3.CursorType = 0 rs3.CursorLocation = 2 rs3.LockType = 3 rs3.Open() rs3_numRows = 0 %> <% Dim Repeat1__numRows Repeat1__numRows = 10 Dim Repeat1__index Repeat1__index = 0 rs_numRows = rs_numRows + Repeat1__numRows %> Which is fine when I select my prices and then 3 bedrooms, it pulls in all properties with 3 bedrooms, select 2 it pulls back all 2 bedroom properties etc. What I want it to do is if I select 3 bedrooms I want it to pull in all 3,4,5,6,7,8,9 and 10 bedroom properties etc. How can I go about this? What do I need to pass through on the form? Any help would be most welcome.
January 20, 201115 yr <% set rs = Server.CreateObject("ADODB.Recordset") rs.ActiveConnection = Conn If(rs__varbeds < 3) Then rs.Source = "SELECT * FROM prop, section, beds, status WHERE beds = bedsid AND status = statusid AND section = sectionid AND section like '" + Replace(rs__varsection, "'", "''") + "' AND beds like '" + Replace(rs__varbeds, "'", "''") + "' AND status like '" + Replace(rs__varstatus, "'", "''") + "' AND price between " + Replace(rs__varpl, "'", "''") + " and " + Replace(rs__varpu, "'", "''") + " ORDER BY price DESC" Else rs.Source = "SELECT * FROM prop, section, beds, status WHERE beds = bedsid AND status = statusid AND section = sectionid AND section like '" + Replace(rs__varsection, "'", "''") + "' AND beds >= " + Replace(rs__varbeds, "'", "''") + " AND status like '" + Replace(rs__varstatus, "'", "''") + "' AND price between " + Replace(rs__varpl, "'", "''") + " and " + Replace(rs__varpu, "'", "''") + " ORDER BY price DESC" End If rs.CursorType = 0 rs.CursorLocation = 2 rs.LockType = 3 rs.Open() rs_numRows = 0 %> Edited January 20, 201115 yr by Jock
Create an account or sign in to comment