frank_solo Posted February 29, 2012 Share Posted February 29, 2012 Having trouble making this work. I have a form that should search the min and max ranges of the rent of apartments. Can anyone please help me with the php script. I think I'm using the MIN and MAX functions incorrectly. Thanks The Form: <form method="post" action="searchapts.php"> Borough: <label for="county" /> <select name="county" id="county"> <option selected="selected">Bronx</option> <option>Brooklyn</option> <option>Manhattan</option> <option>Queens</option> <option>Staten Island</option> <option>---------------</option> <option>Nassau</option> <option>Suffolk</option> </select> <br /> Rooms: <label for="type" /> <select name="type" id="type"> <option>0 Bed</option> <option>1 Bed</option> <option>2 Bed</option> <option>3 Bed</option> <option>4 Bed</option> <option>5 Bed</option> </select> <br /> Rent: <label for="rent" /> <select name="rent" id="rent"> <option value="1000" selected="selected">Less than $1000</option> <option value="1500">$1000 - $1500</option> <option value="2000">$1500 - $2000</option> <option value="2500">$2000 - $2500</option> <option value="3000">$2500 - $3000</option> <option value="3500">$3000 - $3500</option> <option value="4000">$3500 - $4000</option> <option value="4500">$4000 - $4500</option> <option value="5000">$4500 - $5000</option> <option value="5500">$5000 - $5500</option> <option value="6000">$5500 - $6000</option> <option value="6500">$6000 - $6500</option> <option value="7000">$6500 - $7000</option> <option value="7500">$7000 - $7500</option> <option value="8000">$7500 - $8000</option> <option value="8500">$8000 - $8500</option> <option value="9000">$8500 - $9000</option> <option value="9500">$9000 - $9500</option> <option value="10000">$9500 - $10000</option> <option value="10500">$10000 - $10500</option> <option value="11000">$10500 - $11000</option> <option value="11500">$11000 - $11500</option> <option value="12000">$11500 - $12000</option> <option value="12500">$12000 - $12500</option> <option value="13000">$12500 - $13000</option> <option value="13500">$13000 - $13500</option> <option value="14000">$13500 - $14000</option> <option value="14500">$14000 - $14500</option> <option value="15000">$14500 - $15000</option> <option value="15500">$15000 - $15500</option> <option value="16000">$15500 - $16000</option> <option value="16500">$16000 - $16500</option> <option value="17000">$16500 - $17000</option> <option value="17500">$17000 - $17500</option> <option value="18000">$17500 - $18000</option> <option value="18500">$18000 - $18500</option> <option value="19000">$18500 - $19000</option> <option value="19500">$19000 - $19500</option> <option value="20000">$19500 - $20000</option> <option value="25000">$20000 - $25000</option> <option value="30000">$25000 - $30000</option> </select> <br /> <input name="Search" id="Search" value="Search" type="submit" /> </form> The Script: <?php if ($_POST){ $county = $_POST['county']; $rooms = $_POST['type']; $MIN = $_POST['min_price']; $MAX = $_POST['max_price']; } $dbase = mysql_connect ( 'localhost', '', '' ); mysql_select_db ( 'apts', $dbase ); if($county){ $sql = "SELECT * FROM `apartments` WHERE `county` = '".mysql_real_escape_string($county)."' AND `rooms` = '".mysql_real_escape_string($rooms)."' BETWEEN `rent` = '".mysql_real_escape_string($MIN)."' AND `rent` = '".mysql_real_escape_string($MAX)."' order by `date_created` DESC"; }else{ $sql = "SELECT * FROM `apartments`"; } $res = mysql_query($sql, $dbase); if ( mysql_num_rows($res) > 0 ) { echo "<strong>Click Headers to Sort</strong>"; echo "<table border='0' align='center' bgcolor='#999969' cellpadding='3' bordercolor='#000000' table class='sortable' table id='results'> <tr> <th> Title </th> <th> Borough </th> <th> Town </th> <th> Phone </th> <th> Rooms </th> <th> Bath </th> <th> Fees </th> <th> Rent </th> </tr>"; while($row = mysql_fetch_assoc($res)) { echo "<tr> <td bgcolor='#FFFFFF' style='color: #000' align='center'> <a href='classified/searchapts/index.php?id=".$row['id']."'>" . $row['title'] . "</a></td> <td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['county'] . "</td> <td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['town'] . "</td> <td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['phone'] . "</td> <td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['rooms'] . "</td> <td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['bath'] . "</td> <td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['feeornofee'] . "</td> <td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['rent'] . "</td> </tr>"; } echo "</table>"; print_r($apts); } else { echo "<p> </p><p> </p> No Results <br /><p> </p><FORM><INPUT TYPE='button' VALUE='Go Back' onClick='history.go(-1);return true;'></FORM> and Refine Your Search <p> </p><p> </p>"; } ?> Thanks Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/ Share on other sites More sharing options...
batwimp Posted February 29, 2012 Share Posted February 29, 2012 Can you echo out your populated $sql variable? Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322476 Share on other sites More sharing options...
frank_solo Posted February 29, 2012 Author Share Posted February 29, 2012 I get this error Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322477 Share on other sites More sharing options...
Maq Posted February 29, 2012 Share Posted February 29, 2012 I get this error Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource He meant actually echo the $sql value. Put or die at the end to see the error: $res = mysql_query($sql, $dbase) or die(mysql_error()); - Remove the '=' after rooms in your query. - Where are 'min_price' and 'max_price' in your HTML form? Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322478 Share on other sites More sharing options...
frank_solo Posted February 29, 2012 Author Share Posted February 29, 2012 Sorry I gave you the wrong html form it is this <form method="post" action="searchapts.php"> Borough: <label for="county" /> <select name="county" id="county"> <option selected="selected">Bronx</option> <option>Brooklyn</option> <option>Manhattan</option> <option>Queens</option> <option>Staten Island</option> <option>---------------</option> <option>Nassau</option> <option>Suffolk</option> </select> <br /> Rooms: <label for="type" /> <select name="type" id="type"> <option>0 Bed</option> <option>1 Bed</option> <option>2 Bed</option> <option>3 Bed</option> <option>4 Bed</option> <option>5 Bed</option> </select> <br /> Rent: <label for="rent" /> <input name="min_price" type="text" id="min_price" size="7" maxlength="7" /> (Min) <input name="max_price" type="text" id="max_price" size="7" maxlength="7" /> (Max)<br /> <input name="Search" id="Search" value="Search" type="submit" /> </form> The error is this : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '1000' AND `rent` = '3000' order by `date_created` DESC' at line 1 So I should remove the "=" after rooms? Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322484 Share on other sites More sharing options...
batwimp Posted February 29, 2012 Share Posted February 29, 2012 I think the BETWEEN in your sql query is in the wrong place. Try this: $sql = "SELECT * FROM `apartments` WHERE `county` = '".mysql_real_escape_string($county)."' AND `rooms` = '".mysql_real_escape_string($rooms)."' AND `rent` BETWEEN ".mysql_real_escape_string($MIN)." AND ".mysql_real_escape_string($MAX)." order by `date_created` DESC"; Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322491 Share on other sites More sharing options...
frank_solo Posted February 29, 2012 Author Share Posted February 29, 2012 P E R F E C T batwimp Now if I wanted to also add square footage can I do something like this: if ($_POST){ $county = $_POST['county']; $rooms = $_POST['type']; $MIN = $_POST['min_price']; $MAX = $_POST['max_price']; $MINSQ = $_POST['min_square']; $MAXSQ = $_POST['max_square']; } $dbase = mysql_connect ( 'localhost', 'genesis_frank', '5150vh' ); mysql_select_db ( 'genesis_apts', $dbase ); if($county){ $sql = "SELECT * FROM `apartments` WHERE `county` = '".mysql_real_escape_string($county)."' AND `rooms` = '".mysql_real_escape_string($rooms)."' AND `rent` BETWEEN ".mysql_real_escape_string($MIN)." AND ".mysql_real_escape_string($MAX)." ".mysql_real_escape_string($MINSQ)." AND ".mysql_real_escape_string($MAXSQ)." order by `date_created` DESC"; Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322497 Share on other sites More sharing options...
batwimp Posted February 29, 2012 Share Posted February 29, 2012 You're missing something like: AND square_feet BETWEEN there in your SQL query. Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322501 Share on other sites More sharing options...
frank_solo Posted February 29, 2012 Author Share Posted February 29, 2012 Cool Thanks Quote Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322502 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.