Re: [PHP] Select Query with Multiple Optional Values

[Robert Cummings]

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.
> >
> >  >
> > $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


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Select Query with Multiple Optional Values

[Virgilio Quilario]

>> 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.
>
> 
> $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.

Virgil
http://www.jampmark.com
Free tips, tutorials, innovative tools and techniques useful for
building and improving web sites.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] Select Query with Multiple Optional Values

[Robert Cummings]

On Sat, 2009-03-21 at 10:30 -0700, ben...@gmail.com 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.

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.
-- 
http://www.interjinn.com
Application and Templating Framework for PHP


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php