Re: Select Query with Multiple Optional Values

On Sun, 2009-03-22 at 09:22 +0800, Virgilio Quilario wrote: > >> Trying to find best way to accomplish following scenario. Looking to search > >> inventory through a POST form, have the following optional fields to search > >> by: Product Name, Color, Size, and Status. Search maybe for the Product > >> Name and/or Color or they may search for just the Color or all 4 fields. I > >> am trying to find the most efficient way to do this without having 100 > >> different if statements. > > > > <?php > > > > $where = array( '1 = 1' ); > > > > if( !empty( $_POST['name'] ) ) > > { > > where[] = 'name = '.$db->quote( $_POST['name'] ); > > } > > > > if( !empty( $_POST['colour'] ) ) > > { > > where[] = 'colour = '.$db->quote( $_POST['colour'] ); > > } > > > > if( !empty( $_POST['size'] ) ) > > { > > where[] = 'size = '.$db->quote( $_POST['size'] ); > > } > > > > if( !empty( $_POST['status'] ) ) > > { > > where[] = 'status = '.$db->quote( $_POST['status'] ); > > } > > > > $query = > > "SELECT " > > ." * " > > ."FROM " > > ." inventory " > > ."WHERE " > > ." (".implode( ") AND (", $where ).")"; > > > > ?> > > > > Cheers, > > Rob. > > Yep, that's the way to do it. > Or you may do it this way. > > $fields = array('name','colour','size','status'); > foreach ($_POST as $name => $value) { > if (empty($value)) continue; > if (!in_array($name, $fields, TRUE)) continue; > $where[] = $name . '=' . $db->quote($value); > } > > which is more compact and useful when you have 100 different optional fields. As long as your form field names are the same as your database field names. Also as long as you don't need to post process the submitted values in any way :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP