Page 1 of 1

multiple search criteria - how to handle

Posted: Fri Nov 28, 2008 12:00 am
by MIHAIO
Hello,

The user can select from a city drop-down box either 'All' or a particular city.
I would code the SQL

Code: Select all

If city = 0 // the user selected all
    Select * from users
else
     Select * from users where city = '$city'
Is there a 'smart' way to have only one but comprehensive SQL instruction.

The above example is simple but the search form will have many more search criteria so it would be cumbersome to have
so many ifs like

Code: Select all

If city = 0 and make = 0 // the user selected all
    Select * from users
elseif city =0
     Select * from users where make = '$make'
elseif
     Select * from users where city = '$city' and make = '$make'
and so on
Any ideas ?

Thanks

Re: multiple search criteria - how to handle

Posted: Fri Nov 28, 2008 12:11 am
by requinix
Readability is a great thing too. Don't sacrifice it for small and compact code.

There's only so much you can do. There needs to be logic for each different possibility. I prefer the method you have where different circumstances give different queries, but there are other ways of doing it.
You could generate an array of conditions, then use implode and attach it to the incomplete query, like

Code: Select all

$conditions = array();
if ($a) $conditions[] = "1=1";
if ($b) $conditions[] = "2=2";
 
if (!$conditions) {
    // no search criteria
} else {
    $query = "SELECT fields FROM table WHERE " . implode(" AND ", $conditions);
    // run query
}

Re: multiple search criteria - how to handle

Posted: Fri Nov 28, 2008 2:59 am
by pcoder
OR you may try this method:

Code: Select all

 
$Sql = "SELECT * FROM TABLE_NAME WHERE 1=1";
if(isset($_POST['FLD_NAME1']) && trim($_POST['FLD_NAME1'])!='')
  $Sql.=" AND FLD_NAME1=' ".mysql_real_escape_string($_POST['FLD_NAME1'])." ' ";
 
//And so on...
 
 
Cheers

Re: multiple search criteria - how to handle

Posted: Fri Nov 28, 2008 6:04 pm
by califdon
How about:

Code: Select all

if(isset($_POST['city']) && $_POST['city'] != '') {
   $city=mysql_real_escape_string($_POST['city'] ;
} else {
   $city='%' ;
}
if(isset($_POST['make']) && $_POST['make'] != '') {
   $city=mysql_real_escape_string($_POST['city'] ;
} else {
   $make='%' ;
}
...
$sql="SELECT fields FROM table WHERE `city`='$city' AND `make`='$make'";
 

Re: multiple search criteria - how to handle

Posted: Sat Nov 29, 2008 6:14 am
by Kieran Huggins
you could build an array of conditions based on the $_POST data, then implode them thusly:

Code: Select all

$conditions = Array('name'=>'bob', 'score'=>'9000');
 
$c_keys = array_keys($conditions);
$c_vals = array_vals($conditions);
 
$condition_sql = new Array;
 
for($i=0; $i < size_of($conditions), $i++){
  $condition_sql[] = "`" . $c_keys[$i] . '`="' . $c_vals[$i] . '"';
}
 
$sql = "SELECT * FROM wherever";
if(size_of($condition_sql) != 0) $sql .= " WHERE " explode(', ',$condition_sql);
WARNING: the above code is not only untested, but I've also been working almost entirely in Ruby for the better part of a year now. (i.e. it's probably wrong)

Re: multiple search criteria - how to handle

Posted: Sat Nov 29, 2008 11:22 am
by califdon
MIHAIO: As you can see, there are many ways to achieve the same purpose. Some may work better in certain situations, others in different situations. As is true in nearly all programming, the more you learn how to use a language, the better you will be able to solve your problems using the best technique for the purpose at hand.