Creating dynamic where clauses in mysql

Question

Is it possible to dynamically modify the where criteria based on what the user selects? I'm creating a form that has a few dropdowns. For example, given the query below, a client can search the database for properties based on price, neighorhood and type of property (i.e. condo, townhome, single family, etc...). However, there's also an option to return ANY type of property but take the other criteria into consideration. Is there a way to dynamically modify the where clause depending on whether a user wants to return all property type?

SELECT a.id, property_id, price, name as section_name, baths, beds 
FROM 
    properties a, subdivision b
WHERE 
    a.subdivision = b.id and
    property_type = $property_type
ORDER by id"

Answer 1

Just build your query dynamically. I'm not a PHP coder so this probably isn't syntactically correct but it should look something like this:

$query = <<<QUERY
SELECT a.id, property_id, price, name as section_name, baths, beds 
FROM 
    properties a, subdivision b
WHERE 
    a.subdivision = b.id
QUERY;

if ($property_type)
{
   $query = $query . " and property_type = $property_type ";
}

$query = $query . " order by id";

$result = mysql_query($query);

Answer 2

If you code in Java, have a look at iBatis - it has very good support for dynamic queries.

http://ibatis.apache.org/