How to return all queries if user specifies all

rooks · #1 (**permalink**) 12-31-10, 08:57

Hello,

I am trying to run a MySQL query against a property database from an html form drop down list. This is fine if the user selects one of the specific drop down values since i can compare the value against the value stored in the database. It is also fine if the user selects 'Show All' from one of the numeric fields but how do I code the MySQL if the user selects 'Show All' against a text column? For example if they want to see properties > 500 with at least 2 bedrooms but for ANY PROPERTY TYPE?

I have provided the SQL below which works if a property type is specified, but obviously won't work if 'All types' is selected

HTML Code:

<!--html-->
<p>Property Type 
<select name="proptype" size="1">
<option value="Show all properties">All types</option>
<option value="detached">detached</option>
<option value="semi-detached">semi-detached</option>
</select></p>

Code:

$sql = 
"SELECT table1.id, table1.price, table1.beds, table1.type
FROM table1 WHERE 
(table1.price >= $minprice) AND
(table1.price <= $maxprice) AND
(table1.beds >= $bedrooms) AND
(table1.type = '$proptype')";

Any help much appreciated

Thanks a lot

Col

r937 · #2 (**permalink**) 12-31-10, 09:36

in your query, the WHERE clause conditions act as filters

if the user selects the "all" option for a particular data item, then what you do is simply exclude the filter condition for that item, and you automatically get all

it-iss.com · #3 (**permalink**) 12-31-10, 10:45

As r937 has mentioned the WHERE filters the results returned. There are two posibilities:
1. Dynamically generating the where clause using something like:

Code:

$sql = "SELECT table1.id, table1.price, table1.beds, table1.type FROM table1";
$where = "";
if(isset($minprice) && $minprice >= 0) {
  $where .= ($where == "")? " (table1.price >= $minprice)": " and (table1.price >= $minprice)";
}
if(isset($maxprice) && $maxprice >= 0) {
  $where .= ($where == "")? "(table1.price <= $maxprice)": " and (table1.price <= $maxprice)";
}
if(isset($beds) && $beds > 0) {
  $where .= ($where == "")? "(table1.beds <= $bedrooms)": " and (table1.beds <= $bedrooms)";
}
if(isset($type) && $type != "") {
  $where .= ($where == "")? "(table1.type = '$prototype')": " and (table1.type = '$prototype')";
}
$sql .= (($where == "")?"":" WHERE" . $where);

or

2. Write the SQL as follows:

I am assuming that all the PHP variables are set to "" if no value has been added.

Code:

$sql = 
"SELECT table1.id, table1.price, table1.beds, table1.type
FROM table1 WHERE 
('$minprice' = '' or table1.price >= $minprice) AND
('$maxprice' = '' or table1.price <= $maxprice) AND
('$bedrooms' = '' or table1.beds >= $bedrooms) AND
('$prototype' = 'Show all properties' or table1.type = '$proptype')";

Internet Services

Web Development

Digital Marketing

Consumer Tech