multi-field_search_using_PHP

[mrcoma]

I have a form consisting of 9 fields. Through this form i want users to be able to make searches with all combinations of fields against a database, but this requires lots of sql statements for every distinct combination of field selection.. I've tried to use if phrases in sql statements but it didn't work. Is there any other way of doing optional_multi-field searches without coding hundereds of sql statements..
thanks for your time..
sinan..

[Anon]

You could build up your SQL statement dynamically. Something like

$sql="select * from mytable";
unset($whereclauses);
if(isset($field1))
{ $whereclauses[]="field1='$field1'";
}
if(isset($field2))
{ $whereclauses[]="field2=$field2";
}
...
if(isset($field9))
{ $whereclauses[]="field9='$field9'";
}
$where = join(' or ',$whereclauses);
if($where!='')
{ $sql=$sql." where $where";
}

would, if the fields
field1=rice
field2=87
field6=nappies

with the other six being empty, produce the SQL statement

"select * from my table where field1='rice' or field2=87 or field6='nappies'".

Needless to say, your code would undoubtedly look different, but the principle is there - instead of constructing all your SQL statements beforehand to cover every possible contingency, do it on the fly using only the bits you need.