How do I Query Min and Max Price in my Query?

[Woody777]

Good Day Guys

 

I have a bit of a urgent problem.

 

Here is my Query:

 

$query = "SELECT distinct Img.propertyId as PropertyId, Title, ImageUrl, Location, Bedrooms, Bathrooms, Parking, Price FROM PROPERTIES as Prop LEFT JOIN IMAGES as Img ON Img.PropertyId = Prop.PropertyId WHERE 1=1 AND Price >=1000 AND Price <=5000 ";

 

 

I have a problem and the problem is here - AND Price >=1000 AND Price <=5000. This works fine but the problem comes as soon as I add more:

 

For example:

 

if I add OR Price >5000 AND Price <=10000 then it displays all my results from 1000 to 10000 and I want it to be if I select 5000 - 10000 it needs to display only the results between 5000 and 10000. 

 

Can someone please help me?

[IanA]

Might not be the most elegant solution, but you could build your query up depending on the result of if statements.

 

i.e

 

$query = "SELECT distinct Img.propertyId as PropertyId, Title, ImageUrl, Location, Bedrooms, Bathrooms, Parking, Price FROM PROPERTIES as Prop LEFT JOIN IMAGES as Img ON Img.PropertyId = Prop.PropertyId WHERE 1=1 AND ";

 

if(PRICESELECTED >= 5000 && PRICESELECTED <= 10000)

{

   $query .= "Price >= 5000 AND Price <= 10000";

}

else if(PRICESELECTED >= 10000 && PRICESELECTED <= 15000)

{

    $query .= "Price >= 10000 AND Price <= 15000";

}

[Woody777]

Hi thank you for the help I am going to give it a try and respond after that 

[Woody777]

Sorry man it does not work

[IanA]

If you echo your query following the changes made, can you provide us the output?

Edited May 21, 2014 by IanA

[Barand]

$minprice = 5000;            // would come from user input

$maxprice = 10000;


$sql = "SELECT DISTINCT

    prop.propertyId

    , Title

    , ImageUrl

    , Location

    , Bedrooms

    , Bathrooms

    , Parking

    , Price

FROM PROPERTIES as Prop

    LEFT JOIN IMAGES as Img ON Img.PropertyId = Prop.PropertyId

WHERE Price BETWEEN $minprice AND $maxprice";

[IanA]

Nice solution, Barand!

[Woody777]

Thank you guys for all the response.

 

It works but I still have an issue with some thing.

 

If I say for instance :

 

$minprice = 1000;            // would come from user input
$maxprice = 10000;

$sql = "SELECT DISTINCT
    prop.propertyId
    , Title
    , ImageUrl
    , Location
    , Bedrooms
    , Bathrooms
    , Parking
    , Price
FROM PROPERTIES as Prop
    LEFT JOIN IMAGES as Img ON Img.PropertyId = Prop.PropertyId
WHERE Price BETWEEN $minprice AND $maxprice";

 

And I choose Min Price form 5000 and a Max Price from 8000 it needs to give me results between 5000 and  8000.

 

How do I get that right?

[IanA]

The query that Barand gave you should do exactly that, as it is using the BETWEEN keyword, it will only return results between the values passed in. Maybe check the user input to ensure the values are being passed through correctly? 

 

Obviously Barand has given you two variables:

 

$minprice = 1000;            // would come from user input
$maxprice = 10000;

 

These were provided as an example only, if you're still using these static values in your query then it will always return that range.