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.