multiple search criteria - how to handle

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
MIHAIO
Forum Newbie
Posts: 7
Joined: Thu Nov 27, 2008 11:46 pm

multiple search criteria - how to handle

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: multiple search criteria - how to handle

Post 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
}
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Re: multiple search criteria - how to handle

Post 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
Last edited by pcoder on Fri Nov 28, 2008 11:57 pm, edited 3 times in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple search criteria - how to handle

Post 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'";
 
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: multiple search criteria - how to handle

Post 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)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: multiple search criteria - how to handle

Post 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.
Post Reply