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 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? 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 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? 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? 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"; 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"; 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. 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 Link to comment https://forums.phpfreaks.com/topic/258004-between-min-and-max/#findComment-1322502 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.