how do you show a search range of 5-10 in a field..

gatorwire

I want to search my database by searching the price range of $100,000-$150,000

I would be using a drop down menu in a form like this....

here is my search.php...

$sql = "SELECT * FROM homes WHERE models LIKE '%$search%'";
if ($_POST['bed'] != "any") {
$sql .= " AND bed = '".$bed."'";
}

if ($_POST['bath'] != "any") {
$sql .= " AND bath = '".$bath."'";
}

if ($_POST['garage'] != "any") {
$sql .= " AND garage = '".$garage."'";
}

$result = @($sql)

or die("<p>Couldn't execute query: <strong>".mysql_error()."</strong></p>");

while($r=mysql_fetch_array($result))
{

$models=$r["models"];
$bed=$r["bed"];
$bath=$r["bath"];
$garage=$r["garage"];
$sqft=$r["sqft"];
$id=$r["id"];

echo "models:$models bed: $bed bath: $bath garage: $garage sqft: $sqft ";

}
?>

first off, how do I value it in the form and how should the code be in search.php?

thanks....

devinemke

a basic example:

<?
$price_array = array(
	'0|99999',
	'100000|149999',
	'150000|199999',
	'200000|249999',
	'250000|300000'
);

echo '
<form action="" method="POST">
<select name="price"> 
<option value="any">any</option>
';

foreach ($price_array as $key => $value)
{
	$explode = explode('|', $value);
	echo '<option value="' . $key . '">$' . number_format($explode[0]) . ' to $' . number_format($explode[1]) . '</option>';
}

echo '
</select> 
<input type="submit" name="submit" value="search">
</form>
';

if (isset($_POST['submit']))
{
	// connect to db then...

$sql = 'SELECT * FROM homes';
if ($_POST['price'] != 'any')
{
	$explode = explode('|', $price_array[$_POST['price']]);
	$sql .= " WHERE price BETWEEN '" . $explode[0] . "' AND '" . $explode[1] . "'";
}

$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result))
{
	print_r($row);
}
}
?>

gatorwire

thanks it works great but I need the form to be in html form, then have it submitted to the search.php code.

how do you set up like that???

for example my index.html with the form...

Price
<select name="price">
<option value="any" 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>

then of course it is submitted to the search.php

$sql = "SELECT * FROM homes WHERE models LIKE '%$search%'";
if ($_POST['bed'] != "any") {
$sql .= " AND bed = '".$bed."'";
}

if ($_POST['bath'] != "any") {
$sql .= " AND bath = '".$bath."'";
}

if ($_POST['garage'] != "any") {
$sql .= " AND garage = '".$garage."'";
}

if ($_POST['price'] != "any") {
$sql .= " AND price= '".$price."'";
}

$result = @($sql)

or die("Couldn't execute query: ".mysql_error()."");

while($r=mysql_fetch_array($result))
{

$models=$r["models"];
$bed=$r["bed"];
$bath=$r["bath"];
$garage=$r["garage"];
$sqft=$r["sqft"];
$id=$r["id"];

echo "models:$models bed: $bed bath: $bath garage: $garage sqft: $sqft ";

}
?>

sorry, I didn't fully explain myself.

gatorwire

rachel2004, can you help me with this?

rachel2004

This should work for you:

$price_upper_bound = $_POST['price'];
switch($price_upper_bound) {
	case "any":
		$range = "";
		break;

case "0":
	$range = " AND (price >= 100000) AND (price <= 150000)";
	break;

case "1":
	$range = " AND (price > 150000) AND (price <= 200000)";
	break;

default:
	$range = "";
}
$sql .= $range;

Notice two things:
1. I didn't use all of the possible cases to this switch statement. I did enough for you to see it in action. You can add to it, of course.

Notice that on some lines I used ">" instead of ">=". This was intentional so none of the ranges overlapped. Mimic that when you add more cases.

Hope this helped.

gatorwire

I tried your code but when I choose the case "1" option it shows a blank page.

the option 0 and any show everything in the database

rachel2004

Can you post for me what you code looks like now?

gatorwire

this is the html drop down menu..

this is the search.php

$sql = "SELECT * FROM homes WHERE models LIKE '%$search%'";
if ($_POST['bed'] != "any") {
$sql .= " AND bed = '".$bed."'";
}

if ($_POST['bath'] != "any") {
$sql .= " AND bath = '".$bath."'";
}

if ($_POST['garage'] != "any") {
$sql .= " AND garage = '".$garage."'";
}

$price_upper_bound = $_POST['price'];
switch($price_upper_bound) {
case "any":
$range = "";
break;

case "0":
    $range = " AND (price >= 0) AND (price <= 50000)";
    break;

case "1":
    $range = " AND (price > 50000) AND (price <= 150000)";
    break;

}
$sql .= $range;
// you could do this for each desired feature of a property; you get the point.

$result = @($sql)
// in your production code, do not use mysql_error(), but for development, go ahead:
or die("Couldn't execute query: ".mysql_error()."");

//grab all the content
while($r=mysql_fetch_array($result))
{
//the format is $variable = $r["nameofmysqlcolumn"];
//modify these to match your mysql table columns

$models=$r["models"];
$bed=$r["bed"];
$bath=$r["bath"];
$garage=$r["garage"];
$sqft=$r["sqft"];
$price_upper_bound=$r["price"];

echo "models:$models bed: $bed bath: $bath garage: $garage sqft: $sqft price: $price_upper_bound ";

}
?>

rachel2004

Try echoing out the $sql variable at this part:

$sql .= $range;
// you could do this for each desired feature of a property; you get the point. 
echo "<li />sql statement is: <strong>".$sql."</strong>";

Visually verify that the SQL statement looks correct as you'd expect it to be. If you can connect to your MySQL db, you should also try manually entering that query as it has been echoed from the $sql variable. See what results you get back.

Now, if some of your choices in the drop down menu do not show anything or they show everything, check in your database whether you actually have homes that fall in those price ranges or if all of your homes are within the prices ranges for case "1" or "any". That might be why all homes match up.

rachel2004

I looked over your code again. You need to use the "default" case in the switch statement as I had shown you in my other post:

$price_upper_bound = $_POST['price'];
switch($price_upper_bound) {
	case "any":
		$range = "";
		break;

case "0":
	$range = " AND (price >= 0) AND (price <= 50000)";
	break;

case "1":
	$range = " AND (price > 50000) AND (price <= 150000)";
	break;

default: 
	$range = "";
}

gatorwire

I changed the prices of the homes to match up better but only "any" and "0" show up everything.

"1" shows nothing.

What type should the price be? I have it under varchar, should it be under something else?

You can see the results here...

www.gatorwire.com/play

rachel2004

For the prices, use INT, not VARCHAR. By the way, I tested the link you sent and I got this:

SELECT * FROM homes WHERE models LIKE '%%' AND bed = '2' AND bath = '2' AND garage = '2' AND (price > 50000) AND (price <= 150000)

You may want to revisit the part of your code where LIKE '%%' is supposed to be something. It seems that $search is empty, but I'm not sure what you intended it to be. Anyway, alter your database table to use "int" as I suggested and try running your PHP page again.

gatorwire

it works!!!!! thank you.

but now I have another problem, it seems that int will not let me use commas for large prices as 100,000.

That's probably an easy fix.

I tried taking out the %search% but then nothing works.....

here is the top code.....

//db connect
$search=$_GET["models,bed,bath,garage,price"];

$sql = "SELECT * FROM homes WHERE models LIKE '%$search%'";

rachel2004

For storing numeric values, don't use commas. You can use decimal points, (like 250.00), but then you're working with a data type of FLOAT instead of INT. That may not mean anything to you now, but the distinction means something when you start dealing with precision which is important in mathematic/engineering/etc. But anyway, this is not computer science (no pun intended), so just remember not to use commas when STORING numeric values. If you want to output to the screen with commas, that should be okay.

As far as this part:

$search=$_GET["models,bed,bath,garage,price"];

I haven't seen it done like that before (using multiple values between the brackets as you have it), but if you say it's working, carry on. If you wish to change it, you'll need to find another attribute of the column 'models' that you can match up with. Examples:

...WHERE models LIKE 'townhomes'

... WHERE models LIKE 'condo'

etc.

gatorwire

thanks...

ya later on I might have to change it but hopefully not 😃

I am almost completely finished with my project..... now I am on to manipulating the data and adding my template to it.

thanks again for all your help.

rachel2004

You're welcome. 🙂