building dynamic SQL in PHP

[dinosoup]

I am building a search function for real estate properties as an exercise. This is how the search form looks like:
http://img127.imageshack.us/img127/5296/searchformkg6.gif

This is my first time building such a search function. I was wondering if this is how people construct the SQL from the search form? Is there a more efficient way? Thanks.

if ( isset($_GET['search']) )
{
	$fixed_sql = 'FROM property, location, picture WHERE property.lid = location.lid AND property.pid = picture.pid AND location.country = "'.$_GET['country'].'"';
	$dynamic_sql = '';

if ( isset($_GET['province']) && $_GET['province'] != '')
	$dynamic_sql .= ' AND location.province = "'.$_GET['province'].'"';

if ( isset($_GET['state']) && $_GET['state'] != '')
	$dynamic_sql .= ' AND location.state = "'.$_GET['state'].'"';

if ( isset($_GET['city']) && $_GET['city'] != '' )
	$dynamic_sql .= ' AND location.city = "'.$_GET['city'].'"';

if ( isset($_GET['type']) && $_GET['type'] != '' )
	$dynamic_sql .= ' AND property.type = "'.$_GET['type'].'"';

if ( isset($_GET['beds']) && $_GET['beds'] != '' && $_GET['beds'] != 'any' )
{
	if ( $_GET['beds'] >= 0 && $_GET['beds'] <= 8 )
		$dynamic_sql .= ' AND property.num_bedrooms = "'.$_GET['beds'].'"';
	else
		$at_least_num_bedrooms = $_GET['beds'] - 10;
		$dynamic_sql .= ' AND property.num_bedrooms >= "'.$at_least_num_bedrooms.'"';
}

if ( isset($_GET['baths']) && $_GET['baths'] != '' && $_GET['baths'] != 'any' )
{
	if ( $_GET['baths'] >= 0 && $_GET['baths'] <= 5 )
		$dynamic_sql .= ' AND property.num_bathrooms = "'.$_GET['baths'].'"';
	else
		$at_least_num_bathrooms = $_GET['baths'] - 10;
		$dynamic_sql .= ' AND property.num_bathrooms >= "'.$at_least_num_bathrooms.'"';
}

if ( isset($_GET['pricesFrom']) && $_GET['pricesFrom'] != '' )
	$dynamic_sql .= ' AND property.buy_price >= "'.$_GET['pricesFrom'].'"';

if ( isset($_GET['pricesTo']) && $_GET['pricesTo'] != '' )
	$dynamic_sql .= ' AND property.buy_price <= "'.$_GET['pricesTo'].'"';

if ( isset($_GET['rentFrom']) && $_GET['rentFrom'] != '' )
	$dynamic_sql .= ' AND property.rent_price >= "'.$_GET['rentFrom'].'"';

if ( isset($_GET['rentTo']) && $_GET['rentTo'] != '' )
	$dynamic_sql .= ' AND property.rent_price <= "'.$_GET['rentTo'].'"';

// only return properties that are available (not sold or being rented already)
$dynamic_sql .= ' AND property.available = 1';

$count_sql = 'SELECT COUNT(*) AS num_rows '.$fixed_sql.$dynamic_sql;
$sql = 'SELECT * '.$fixed_sql.$dynamic_sql.' LIMIT '.$offset.','.$result_per_page;
}
?>

[NogDog]

If your form field names and DB column names are identical, you could streamline things a bit as follows:

$whereFields = array('province', 'state', 'city', 'type');
$where = array();
foreach($whereFields as $field)
{
   if (!empty($_GET[$field]))
   {
      $where[] = "location.$field = '{$_GET[$field]}'";
   }
}
if(count($where))
{
   $dynamic_sql .= ' '.implode(' AND ', $where);
}