php search help

Question

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 this.

Well, imo you can just set the default value of your table fields to NULL and "INSERT INTO table VALUES('{$_REQUEST['city']}', ...)" — seriousdev
– seriousdev, Commented Dec 21, 2010 at 11:21
I think it's about SELECT not INSERT, according to the headline. — powtac
– powtac, Commented Dec 21, 2010 at 11:33

Pekka · Accepted Answer · 2010-12-21 11:23:04Z

3

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);

answered Dec 21, 2010 at 11:23

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pekka · Accepted Answer · 2010-12-21 11:29:55Z

1

$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;

edited Dec 21, 2010 at 11:29

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

answered Dec 21, 2010 at 11:22

powtac

41.1k28 gold badges120 silver badges173 bronze badges

1 Comment

Pekka Over a year ago

I took the liberty of adding mysql_real_escape_string(), mysql_escape_string is deprecated

nivanka · Accepted Answer · 2010-12-21 11:21:32Z

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}";

nivanka · Accepted Answer · 2010-12-21 11:25:52Z

1

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.

answered Dec 21, 2010 at 11:25

nivanka

1,3726 gold badges23 silver badges37 bronze badges

Comments

jwueller · Accepted Answer · 2010-12-21 11:24:56Z

0

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).

answered Dec 21, 2010 at 11:24

jwueller

31.1k5 gold badges69 silver badges71 bronze badges

Collectives™ on Stack Overflow

php search help

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related