[adsense id=”0514458240″ width=”468″ height=”60″]
I have been asked how to determine the records to be returned based on the input in a form. However, if a form field contains no value then we want this to be interpreted as no filtering. There are two ways in which this can be done:
- Programmatically through PHP
- Directly in the SQL
This post will provide both ways in which this can be performed. In this case the example is a real estate form consisting of a min price range, a max price range, the number of bedrooms and a type of house (detached, semi-detached etc).
To select all records we would issue the following SQL statement
SELECT table1.id, table1.price, table1.beds, table1.type FROM table1;
Programmatically through PHP
This appears to be the simpler method as we generate the query string dynamically deciding what the WHERE clause should include and/or exclude. For instance we might have:
$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);
The desired SQL query has been generated dynamically and upon being executed only returns the requested rows.
SQL
The second method is within the SQL string. This may appear complex but the reality is that this can be done relatively easily as follows:
$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' = '' or table1.type = '$proptype')";
Basically if $minprice or any of the other variables are blank then we can ignore the second part of the filter operation within the braces. For instance if $minprice is "" this returns TRUE and the row is accepted. If $minprice is a valid number then the first part fails and the second part will determine whether it will work or not.