Search price-range prob

[pucca]

hi, I'm doing a search on a property website. In the database there IS a place where the client can fill in the pricefrom: and the priceto: .. AND theres a field where they can fill in the price... They've uploaded alot of properties, without filling in the priceto: and price from: fields.. but only the Price: ...

So now, I've done a form, where people can search what properties they've got, doing a search with a price range. ex. $0 - $400 000, $401 000 - $700 000 ... etc.. if that makes sense?

How do I create a code, that gets the price ex. $438 000, and put it in the $401 000 - $700 000 price range?

Hope i'm clear? Thx!

[Refined]

Hi pucca,

I think I know what you mean. It will be a lot easier in your search logic just to have the price field, no pricefrom or priceto. When your form gets submitted work out the maximum and minimum prices for the range, E.g. $min = 100000 and $max = 500000. The SQL would be:



SELECT * FROM `properties` WHERE `price` >= '$min' AND `price` <= '$max';


I'm not sure whether you're using PHP or not so this was a generic SQL answer ;)

Hope it helps.

[pucca]

thx.. a step closer. yeah, I'm using php
..ok, thought I had it:

picture this :

page one.. client selects 400 000 - 700 000 from dropdown list.

$400_700 (the variable declared - theres a $701-1000000 aswell) is being brought over to results page.

Now, I say

Select * from tbl_name where price = ..? what?

cause I can set a $min = $400000 and $max = $700000 but it might as well be $701_100000 thats gets brought over?

am I clear?

[pucca]

ok.. i'm doing a seperate sql select statement for each one.. WHAT is wrong with this statement?????


$suburb = $_REQUEST['area'];
$price = $_REQUEST['price'];

if ($_REQUEST['price'] == '401-700') {

$min = 401000;
$max = 700000;


$qrySelect = "SELECT * FROM WB_listings, PDS_listings, WB_users WHERE '" . $suburb . "' = Psuburb AND price >= '" . $min . "' AND price <= '" . $max . "'";

$qrySelect1 = mysql_query($qrySelect, $connect);
$rows = mysql_fetch_array($qrySelect1);
}

[Refined]

Hi Pucca,

If you are using $_POST variables it's better to use $_POST['variable'] rather than $_REQUEST.

Your SQL is slightly wrong, you need to specify what is coming from what tables. Try this:



$qrySelect = "SELECT `WB_listings`.*, `PDS_listings`.*, `WB_users`.* FROM `WB_listings`, `PDS_listings`, `WB_users` WHERE `PDS_listings`.`Psuburb` = '".$suburb."' AND (`WB_listings`.`price` >= '".$min."' AND `WB_listings`.`price` <= '".$max."')";
That query is assuming that the 'Psuburb' column is from the 'PDS_listings' table and the 'price' column is from the 'WB_listings' table. Alter as necessary. The other thing that was changed is that your 'WHERE '" . $suburb . "' = Psuburb' part was the wrong way around.

Note that the 'tick' quotes (`) are not absolutely necessary, it's just good practice to use the correct quotes wherever you can.

Hope it works.

[icio]

I would suggest you change how you structure the way that you're taking the info from the drop-down (or radio buttons?) on the form that allow you to select the price range. Here's how I would consider doing it:

$area = $_POST['area'];
$price = $_POST['price'];

list($min, $max) = explode('-',$price);
$min = intval($min)*1000;
$max = intval($max)*1000;

$query = "
SELECT * FROM
`WB_listings`,
`PDS_listings`,
`WB_users`
WHERE
`PDS_listings`.`Psuburb` = '".$area."' AND
(
`WB_listings`.`price` >= '".$min."' AND
`WB_listings`.`price` <= '".$max."'
)";
$result = mysql_query($query);

if (!$result)
{ // => Query Error
die(mysql_error());
}
while ($property = mysql_fetch_assoc($result))
{
print_r($property);
}

This will allow you to send any price-range in the format:

10-20
Which will generate

$min = 10000;
$max = 20000;

So that all of the other coding you need to do is create your form, because the PHP will do the rest dynamically.

<select name="price">
<option value="0-50">0&rarr;50,000</option>
<option value="50-150">50,000&rarr;50,000</option>
<option value="150-300">150,000&rarr;300,000</option>
<option value="300-600">300,000&rarr;600,000</option>
</select>

Hope that helps.

[Charleh]

SELECT * FROM
`WB_listings`,
`PDS_listings`,
`WB_users`
WHERE
`PDS_listings`.`Psuburb` = '".$area."' AND
(
`WB_listings`.`price` >= '".$min."' AND
`WB_listings`.`price` <= '".$max."'
)";

Isn't that query just one big cross join? There's no relationship between the 3 tables...that's not going to give you back the results you want

That will give you all the results from PDS_listings that are filtered by area, but it will give you back any rows in WB_listings that match the mix/max criteria PER row in PDS_listings and it will also give you back all rows in WB_users PER row in WB_listings so you will get PDS_listings * WB_users * WB_listings number of rows...

Surely you need to either use a join or relate the tables to each other in the where clause? I don't understand what that query is trying to achieve..?! Is there no relationship between the 3 tables?

[mpeacock]

After years of working with Oracle, BETWEENs in MySQL are like an old friend. This should work in MySQL 4+ and is easier to debug than a double test (as in price >= min AND price <= max).



$query = "
SELECT * FROM
`WB_listings`,
`PDS_listings`,
`WB_users`
WHERE
`PDS_listings`.`Psuburb` = '".$area."' AND
(
`WB_listings`.`price` BETWEEN '".$min."' AND '".$max."'
)";


FM here: http://dev.mysql.com/doc/refman/4.1/en/comparison-operators.html#function_between

[icio]

I was thinking of `BETWEEN`, but I didn't think the search was inclusive - turns out it is :)