[RESOLVED] sql data filters

[jeepin81]

I am working on a page where people can filter our product by selecting different checkboxes... Color, Size, Finish, Brand, etc.... Is there a better way to write a SQL command instead of doing if/else for each possible scenario the person is filtering by.

For Example I am currently doing the following:

if($color != false && $brand != false && $finish != false){
$sql = "SELECT d4item, psdesc, itmsgnbrnd, itmsgnmclr, itmsgnmfin FROM ( 
					SELECT d4item, psdesc, itmsgnbrnd, itmsgnmclr, itmsgnmfin, ROW_NUMBER() OVER (ORDER BY d4item ASC) 
					AS RowNum 
					FROM $schema.drill4
					JOIN $schema.itemmast ON iitem = d4item
					JOIN $schema.itempict ON pitem = d4item 
					JOIN $schema.itemsale ON d4item = sitem AND ico = '1' AND istats <> 'V' AND isell > 0
					JOIN $schema.itmmktsgn ON d4item = itmnbr
					WHERE
					itmsgnbrnd = '".db2_escape_string($brand)."'
					AND ucase(itmsgnmclr) IN (".$cColors.")
					AND ucase(itmsgnmfin) = '".db2_escape_string($finish)."'	
					) AS Data 
					WHERE RowNum BETWEEN '".db2_escape_string($start)."' AND '".db2_escape_string($end)."'";
}
elseif($color != false && $brand != false && $finish == false){
$sql = "SELECT d4item, psdesc, itmsgnbrnd, itmsgnmclr, itmsgnmfin FROM ( 
					SELECT d4item, psdesc, itmsgnbrnd, itmsgnmclr, itmsgnmfin, ROW_NUMBER() OVER (ORDER BY d4item ASC) 
					AS RowNum 
					FROM $schema.drill4
					JOIN $schema.itemmast ON iitem = d4item
					JOIN $schema.itempict ON pitem = d4item 
					JOIN $schema.itemsale ON d4item = sitem AND ico = '1' AND istats <> 'V' AND isell > 0
					JOIN $schema.itmmktsgn ON d4item = itmnbr
					WHERE
					itmsgnbrnd = '".db2_escape_string($brand)."'
					AND ucase(itmsgnmclr) IN (".$cColors.")
					) AS Data 
					WHERE RowNum BETWEEN '".db2_escape_string($start)."' AND '".db2_escape_string($end)."'";
}
else{
  //etc....
}

Basically, I am wondering if there is a way to have 1 SQL statement that contains all of the filter params. Maybe I set the values of the != params to NULL? Just seem really repetitive the direction I am going. I appreciate any insight.

[pbismad]

Build the terms in an php array variable, then simply implode the array using ' AND ' to dynamically build that part of the WHERE clause. This will work for a single term (implode will return the single term AS IS.)

[jeepin81]

Thanks for the reply. I didn't use implode(); instead just 2 arrays and just build the query up by concatenating the string as it loops through. Seems to be working.