How can I specify a search for varible LESS THAN a specific #

sgtpepper

I am creating a search form that searches mysql query. I have a field that has numbers. (Although I have mysql configured for this column to be varchar - not sure if that matters).

I would like to have my query search for a range of numbers

What do I need to do to make this form work propberly. Where is have "Between 100000 and 150000" I would like to know, how I should set up the form so that it will only bring back results if the numbers match that range.

Thank you for the help.

John_Canyon

WHERE num > '".$yournum."' AND num < '".$othernum."'

sgtpepper

Hmmm...I am a little confused...my query looks like this.

$query="SELECT * FROM residential WHERE list_price like '%$selectprice%'";

My html form is this:

So based on your answer...how can I get the results I am looking for?? Thanks...

kevinsequeira

$query="SELECT * FROM residential WHERE list_price".$selectprice;
// echo $query to debug ....
echo $query;
My html form is this:

<td width="20%"><select size="7" name="selectprice">
<option value="">Any</option>
<option value="= 100,000">$100,000 or less</option>
<option value=">100000 and list_price<150000">$100,000 to $150,000</option>
<option value=">150000 and list_price<200000">$150,000 to $200,000</option>
<option value=">200000 and list_price<250000">$200,000 to $250,000</option>
<option value=">250000 and list_price<300000" selected>$250,000 to
$300,000</option>

reg
kevin

John_Canyon

ahh, you need me to write the code for you eh?


$range = array(
0 => "num < 100000",
1 => "num < 150000 AND num > 100000",
2 => "num < 200000 AND num > 150000",
3 => "num < 250000 AND num > 200000",
4 => "num < 300000 AND num > 250000",
);

$sql = mysql_query("SELECT * FROM yourtable WHERE ".$range[$selectprice]);

Something similar to this should work.

Cheers

sgtpepper

I am having some difficulty getting the array to work. Could you please take a look at the code and offer a suggestion?

test.php

<?
$username="XXX";
$password="XXX";
$database="XXX";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM residential WHERE town like '%$selecttown%' AND list_price like '%$selectprice%'";

$result=mysql_query($query) or die (mysql_error());
$num=mysql_numrows($result);

$range = array(
0 => "num < 100000",
1 => "num < 150000 AND num > 100000",
2 => "num < 200000 AND num > 150000",
3 => "num < 250000 AND num > 200000",
4 => "num < 300000 AND num > 250000",
);

$sql = mysql_query("SELECT * FROM yourtable WHERE ".$range[$selectprice]);
?>

<?
if(mysql_numrows($result) == '0') {
echo "Sorry, no results were found...";
} else {
// display results here
}
?>

<?
while ($row = mysql_fetch_array($result)) // this will grab the results from query
{
?>
<table border="0" width="100%">
<tr>
<td width="12%"><img border="0" src="http://www.XXX.com/listing_img/<? echo $row['Id']; ?>1.jpg" width="100" height="59"></td>
<td width="12%" align="center"><b><? echo $row['Id']; ?></b></td>
<td width="12%" align="center"><? echo $row['town']; ?></td>
<td width="12%" align="center"><? echo $row['rooms']; ?></td>
<td width="13%" align="center"><? echo $row['bedrooms']; ?></td>
<td width="13%" align="center"><? echo $row['baths']; ?></td>
<td width="13%" align="center"><? echo $row['list_price']; ?></td>
<td width="13%" align="center">

<a href="test2.php?Id=<? echo $row['Id']; ?>">Click to view more</a>

</td>

</tr>
</table>

<?
}
?>

HTML FORM PAGE test.html

<table border="0" width="63%">
<tr>
<td width="100%">
<table border="0" width="100%">
<tr>
<td width="20%" bgcolor="#FFFF99" align="center"><strong><font face="Tahoma" size="2">Town</font></strong></td>
<td width="20%" bgcolor="#FFFF99" align="center"><strong>
<font face="Tahoma" size="2">Price Range</font></strong></td>
<td width="20%" bgcolor="#FFFF99" align="center"><strong>
<font face="Tahoma" size="2">Bed Rooms</font></strong></td>
<td width="20%" bgcolor="#FFFF99" align="center"><strong>
<font face="Tahoma" size="2">Baths Rooms</font></strong></td>
</tr>
<tr>
<td width="20%"><select size="7" name="selecttown" multiple>
<option value="claremont">Claremont, NH</option>
<option value="Cornish NH">Cornish, NH</option>
<option value="Croydon NH">Croydon, NH</option>
<option value="Enfield NH">Enfield, NH</option>
<option value="Newport NH">Newport, NH</option>
<option value="Sunapee NH">Sunapee, NH</option>
<option value="Unity NH">Unity, NH</option>
<option value="Windsor VT">Windsor, VT</option>
<option value="Woodstock VT">Woodstock, VT</option>
</select></td>
<td width="20%"><select size="7" name="selectprice">
<option value="" selected>Any</option>
<option value="0">$100,000 or less</option>
<option value="1">$100,000 to $150,000</option>
<option value="2">$150,000 to $200,000</option>
<option value="3">$200,000 to $250,000</option>
<option value="4">$250,000 to $300,000</option>
</select></td>
<td width="20%"><font face="Courier"><input type="radio" CHECKED value="0" name="roomselect">Any<br>
<input type="radio" value="1" name="SelRooms">1+ <br>
<input type="radio" value="2" name="SelRooms">2+ <br>
<input type="radio" value="3" name="SelRooms">3+ <br>
<input type="radio" value="4" name="SelRooms">4+ </font></td>
<td width="20%"><font face="Courier"><input type="radio" CHECKED value="0" name="bathselect">Any<br>
<input type="radio" value="1" name="SelBaths">1+ <br>
<input type="radio" value="2" name="SelBaths">2+ <br>
<input type="radio" value="3" name="SelBaths">3+ </font></td>
</tr>
</table>
<p align="center"><input type="submit"><input type="reset"></p>
</form>
</td>
</tr>
</table>
</form>

Thanks for any help you can give.

kevinsequeira

change test.php to this

<?
$username="XXX";
$password="XXX";
$database="XXX";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$range = array(
0 => "num < 100000",
1 => "num < 150000 AND num > 100000",
2 => "num < 200000 AND num > 150000",
3 => "num < 250000 AND num > 200000",
4 => "num < 300000 AND num > 250000",
);
$selectprice = $_POST['selectprice'];
$sql = mysql_query("SELECT * FROM yourtable WHERE ".$range[$selectprice]);
echo $sql;
$result=mysql_query($query) or die (mysql_error());
if(mysql_numrows($result) == '0') {
echo "Sorry, no results were found...";
} else {
// display results here
     while ($row = mysql_fetch_array($result)){
?>
     <table border="0" width="100%">
     <tr>
     <td width="12%"><img border="0" src="http://www.XXX.com/listing_img/<? echo $row['Id']; ?>1.jpg" width="100" height="59"></td>
     <td width="12%" align="center"><b><? echo $row['Id']; ?></b></td>
     <td width="12%" align="center"><? echo $row['town']; ?></td>
     <td width="12%" align="center"><? echo $row['rooms']; ?></td>
     <td width="13%" align="center"><? echo $row['bedrooms']; ?></td>
     <td width="13%" align="center"><? echo $row['baths']; ?></td>
     <td width="13%" align="center"><? echo $row['list_price']; ?></td>
     <td width="13%" align="center">
     <a href="test2.php?Id=<? echo $row['Id']; ?>">Click to view more</a>
     </td>
     </tr>
     </table>
<?
	}
}
?>

this will only search for price selection, if you want to search for town and room type, u will have to aadd more arrays and modify the query

reg
kevin