Between MIN and MAX

frank_solo · 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

batwimp · February 29, 2012

Can you echo out your populated $sql variable?

frank_solo · February 29, 2012

I get this error Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Maq · 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?

frank_solo · 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?

batwimp · 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";

frank_solo · 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";

batwimp · February 29, 2012

You're missing something like:

AND square_feet BETWEEN

there in your SQL query.

frank_solo · February 29, 2012

Cool Thanks

Sign In

Between MIN and MAX

Recommended Posts

frank_solo

Link to comment

Share on other sites

batwimp

Link to comment

Share on other sites

frank_solo

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

frank_solo

Link to comment

Share on other sites

batwimp

Link to comment

Share on other sites

frank_solo

Link to comment

Share on other sites

batwimp

Link to comment

Share on other sites

frank_solo

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information