Search Forms with Min Price and Max Price

Woody777 · May 19, 2014

Hi

I have this Query and would like to get help on this.

I have a table with Price in it and on my search form I have Min Price and Max Price, I want to connect the min and max price to price so that when people do a search ith will display the results between the min and max price they chose, for instants:

Min Price = 1000 and Max Price = 10000 it have to give me that results between that 1000 and 10000.

$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 ";

Thank you

ginerjm · May 19, 2014

you need a where clause at the end to compare price to the min & max prices values you have. Do a search on sql joins.

Woody777 · May 19, 2014

Ok I see but can't I just add it to that query?

ginerjm · May 19, 2014

Yes you can! Just thought you might not be up for it yet.

just type:

where price >= $minprice and price <= $maxprice

at the end.

Woody777 · May 19, 2014

O yes and there is another thing I am getting a error:

What does it mean?

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\wamp\www\sa property finder\search.php on line 268

ginerjm · May 19, 2014

It means that all of the MySQL_* functions are being removed from php in the near (very?) future. It's listed in the manual for every function and many, many forum posts make the suggestion to newbies to move away from MySQL_query and the like. The way to go is PDO or mysqlI if you have to.

Heed the warning and learn a new set of functions.

Woody777 · May 19, 2014

Ok well thank you for the advice.

As soon as I put the code at the end I get that error. Do you maybe have any other suggestions?

Woody777 · May 19, 2014

I have done something with pdo but that is on my sale and rent page:

include 'database.php';

$pdo = Database::connect();

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT * FROM properties inner join images on images.propertyid = properties.propertyid WHERE Status ='For Sale' group by images.propertyid";

foreach ($pdo->query($sql) as $row) {

$propertyid = $row['PropertyId'];

$pdo = Database::connect();

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql1 = "SELECT * FROM images where PropertyId = ?";

$q1 = $pdo->prepare($sql1);

$q1->execute(array($propertyid));

$data = $q1->fetch(PDO::FETCH_ASSOC);

echo '<li class="col-md-4">';

echo '<div class="proj_block">';

echo '<img alt="" style="opacity: 1" src="'. $row['ImageUrl'].'"></div>';

echo '<div class="proj_descr"><h2>'. $row['Title'] . '</h2>';

echo '<div class="proj_descr_txt">'.'<b>Location: </b> '. $row['Location'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Bed: </b> '. $row['Bedrooms'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Bath: </b> '. $row['Bathrooms'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Parking: </b> '. $row['Parking'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Price: </b>R'. $row['Price'] . '</div>';

echo '<a href="readmore.php?PropertyId='.$propertyid.'">Read More</a>';

echo '</li>';

}

Database::disconnect();

ginerjm · May 19, 2014

ooops! I mis-read the error message earlier. IT means that you didn't check the results of you query run before trying to fetch a record. If you had (or if you had error checking turned on!) you would have read a message and aborted.

Turn on error checking. And add a check on whether the queryresults var is false or not. False is bad - show the error message from MySQL_error (?)

Woody777 · May 19, 2014

But I have and check connection:

// Check connection

if (mysqli_connect_errno()) {

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

ginerjm · May 19, 2014

The connect is nothing. It's the query that you have to verify so you know whether you made a mistake in it.

Woody777 · May 19, 2014

Can I send my whole code for you can you please help me?

ginerjm · May 19, 2014

I think I just need to see where you build your query statement and run your query

Woody777 · May 19, 2014

<?php

$type = $_POST['type'];

$status = $_POST['status'];

$minprice = $_POST['min_price'];

$maxprice = $_POST['max_price'];

$bedrooms = $_POST['bedrooms'];

$baths = $_POST['baths'];

$con=mysqli_connect("localhost","root","","saproperties");

// Check connection

if (mysqli_connect_errno()) {

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

$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 ";

if ($status != ''){

$qStatus = " AND status = '$status' ";

$query = $query . $qStatus;

}

if ($type != ''){

$qType = " AND type = '$type' ";

$query = $query . $qType;

}

if ($baths != ''){

$qBaths = " AND baths = '$baths' ";

$query = $query . $qBaths;

}

if ($bedrooms != ''){

$qBedrooms = " AND bedrooms = '$bedrooms' ";

$query = $query . $qBedrooms;

}

if ($minprice != ''){

$qMinprice = " AND min_price = '$minprice' ";

$query = $query . $qMinprice;

}

if ($maxprice != ''){

$qMaxprice = " AND max_price = '$maxprice' ";

$query = $query . $qMaxprice;

}

$query = $query." GROUP BY PropertyId";

$result = mysqli_query($con,$query);

if ($status == 'For Sale'){

while($row = mysqli_fetch_array($result)) {

echo '<li class="col-md-4">';

echo '<div class="proj_block">';

echo '<img alt="" style="opacity: 1" src="'. $row['ImageUrl'].'"></div>';

echo '<div class="proj_descr"><h2>'. $row['Title'] . '</h2>';

echo '<div class="proj_descr_txt">'.'<b>Location: </b> '. $row['Location'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Bed: </b> '. $row['Bedrooms'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Bath: </b> '. $row['Bathrooms'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Parking: </b> '. $row['Parking'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Price: </b>R'. $row['Price'] . '</div>';

echo '<a href="readmore.php?PropertyId='.$row['PropertyId'].'">Read More</a>';

echo '</li>';

}

}else if ($status == 'For Rent'){

while($row = mysqli_fetch_array($result)) {

echo '<li class="col-md-4">';

echo '<div class="proj_block">';

echo '<img alt="" style="opacity: 1" src="/sa property finder/'. $row['ImageUrl'].'"></div>';

echo '<div class="proj_descr"><h2>'. $row['Title'] . '</h2>';

echo '<div class="proj_descr_txt">'.'<b>Location: </b> '. $row['Location'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Bed: </b> '. $row['Bedrooms'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Bath: </b> '. $row['Bathrooms'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Parking: </b> '. $row['Parking'] . '</div>';

echo '<div class="proj_descr_txt">'.'<b>Price: </b>R'. $row['Price'] . '</div>';

echo '<a href="readmore.php?PropertyId='.$row['PropertyId'].'">Read More</a>';

echo '</li>';

}

mysqli_close($con);

?>

ginerjm · May 19, 2014

Your where clause is close. Don't you want to check <= maxprice and >= minprice?

Anyway - you need to add a check of your query execution before attempting to read the results. look up MySQL_error() function

Woody777 · May 19, 2014

Sorry but I dont understand so clearly?

Like this:

$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 <= maxprice and >= minprice ";

ginerjm · May 19, 2014

I believe you have to say it longhand :

Price <= $maxprice and Price >= $minprice

Woody777 · May 19, 2014

I have tried it but still getting that error

ginerjm · May 19, 2014

THAT'S BECAUSE YOU HAVEN'T LISTENED TO ME!

You have to add a line after the query checking it for an error.

(run the query)

$result = mysqli_query($con,$query);
(test the query ran)
if (!$result)
{
    echo "Query failed to run - error message is: ".MySQL_error();
    exit();
}
// now fetch the results since YOU ARE SURE it ran ok

Woody777 · May 20, 2014

Hi there

I am having an issue on this query, I want to get the min price and max price search to work can anyone help me on this please it is kinda urgent?

Must I have a Min Price and Max Price inside my table for it to work?

I have to connect the Min Price and Max Price to my Price inside my table.

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 BETWEEN >parameterMinPrice AND <parameterMaxPrice ";

Here is the whole PHP Code:

<?php

if (isset($_POST)) {

$status = $_POST['status'];

$type = $_POST['type'];

$bedrooms = $_POST['bedrooms'];

$bathrooms = $_POST['bathrooms'];

$minprice = $_POST['min_price'];

$maxprice = $_POST['max_price'];

}

$con=mysqli_connect("localhost","root","","saproperties");

// Check connection

if (mysqli_connect_errno()) {

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

$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 BETWEEN >parameterMinPrice AND <parameterMaxPrice ";

if ($status != ''){

$qStatus = " AND status = '$status' ";

$query = $query . $qStatus;

}

if ($type != ''){

$qType = " AND type = '$type' ";

$query = $query . $qType;

}

if ($bedrooms != ''){

$qBedrooms = " AND bedrooms = '$bedrooms' ";

$query = $query . $qBedrooms;

}

if ($bathrooms != ''){

$qBathrooms = " AND bathrooms = '$bathrooms' ";

$query = $query . $qBathrooms;

}

if ($minprice != ''){

$qMinprice = " AND min_price = '$minprice' ";

$query = $query . $qMinprice;

}

if ($maxprice != ''){

$qMaxprice = " AND max_price = '$maxprice' ";

$query = $query . $qMaxprice;

}

$query = $query." GROUP BY PropertyId";

$result = mysqli_query($con,$query);

if (!$result)

{

echo "Query failed to run - error message is: ".MySQL_error();

exit();