php search help

following are my posted variables from search form:

$city = $_REQUEST['city'];
$location = $_REQUEST['location'];
$bedrooms = $_REQUEST['noofbedrooms'];
$addeddate = $_REQUEST['addeddate'];
$minprice = $_REQUEST['pricefrom'];
$maxprice = $_REQUEST['priceto'];
$minarea = $_REQUEST['areafrom'];
$maxarea = $_REQUEST['areato'];
$propertytype = $_REQUEST['proptype'];

so far so good. Now i need some good suggestions for the following scenario. Almost every element in my field are optional. That means i can get empty values in above variables.

What should be my scenario to create the mysql query for the above variables. On case can be that i use conditions for each and every scenario. eg

if($city=="")
  $query="";
elseif($location=="")
  $query="";
and so on....

i need some professional approach for thi开发者_C百科s.

If all the query parts are built in the same way: WHERE fieldname='fieldvalue', you could use a lazy, loop-based approach:

$conditions = array();

foreach (array("city", "location", "noofbedrooms") as $field) 
           // ^ add all fields as needed
 {
   // Check whether parameter was passed at all
   if (!array_key_exists($field, $_POST)) continue;

   // Check whether parameter is empty
   if (!empty($_POST[$field]))
    $conditions[]="`$field` = ".mysql_real_escape_string($_POST[$field]);  
                                // ^ or whatever your database library 
                                //   does for escaping  

 }

 $query = "SELECT * from table where ".implode(" AND ", $conditions); 

$city      = mysql_real_escape_string($_REQUEST['city']);
$location  = mysql_real_escape_string($_REQUEST['location']);
$bedrooms  = mysql_real_escape_string($_REQUEST['noofbedrooms']);
$addeddate = mysql_real_escape_string($_REQUEST['addeddate']);
$minprice  = mysql_real_escape_string($_REQUEST['pricefrom']);
$maxprice  = mysql_real_escape_string($_REQUEST['priceto']);
$minarea   = mysql_real_escape_string($_REQUEST['areafrom']);
$maxarea   = mysql_real_escape_string($_REQUEST['areato']);



$query = 'SELECT * FROM hotels WHERE 1 = 1 ';

$query .= strlen($city)     ? ' AND city = "'.$city.'"'         : '';
$query .= strlen($location) ? ' AND location = "'.$location.'"' : '';
$query .= strlen($bedrooms) ? ' AND bedrooms = "'.$bedrooms.'"' : '';
// ... do it for all params ...

echo $query;

you can do something like this

$where = array();

if(strcmp($_REQUEST['city'], "")){
    $where[] = "SEARCH_COLUMN = '" . $_REQUEST['city'] . "'";
}
if(strcmp($_REQUEST['location'], "")){
    $where[] = "SEARCH_COLUMN = '" . $_REQUEST['location'] . "'";
}
if(strcmp($_REQUEST['noofbedrooms'], "")){
    $where[] = "SEARCH_COLUMN = '" . $_REQUEST['noofbedrooms'] . "'";
}
if(strcmp($_REQUEST['addeddate'], "")){
    $where[] = "SEARCH_COLUMN = '" . $_REQUEST['addeddate'] . "'";
}
if(strcmp($_REQUEST['pricefrom'], "")){
    $where[] = "SEARCH_COLUMN = '" . $_REQUEST['pricefrom'] . "'";
}
...... // check for all the fields

when create the SQL using 

$SQL = implode(" OR ", $where);

after that you can use this on some SQL like

"SELECT * FROM WHERE {$SQL}";

well I answered above, I think as your fields are optional you dont need to go with SQL AND, some somtimes = is not that good as well, you can use LIKE for most cases like location, and price from can be a ">=" etc. please note.

Something like this should do:

$fields = array('city', 'location' /* ... */);
$conditions = array();

foreach ($fields as $field) {
    if (!empty($_REQUEST[$field])) {
        $conditions[] = 'column LIKE "%'.mysql_real_escape_string($_REQUEST[$field]).'%"';
    }
}

$query = 'SELECT row FROM table WHERE '.implode(' OR ', $conditions);

You need to adjust this to suit your needs (OR or AND for example).