in reply to mysql search statement

I usually do this like so: 
"SELECT FROM table WHERE ".
  ( $form_username ? "username = $form_username " : '' ).
    ( $form_username && $form_password ? 'AND ' : '' ).
  ( $form_password ? "password = $form_password " : '' ).
    ( ($form_username || $form_password) && $form_name ? 'AND ' : '' )
+.
  ( $form_name ? "name = $form_name" : '' );

[download]
It's not really any shorter than your if-statement version, but my brain finds it easier to read. If you have a number of statements like this, you can write a little sub to generate them. The sub could use a loop, or it could use a variation of the concatenation statement above, for which the general form is: 
    a (or not)
    AND if a and b
    b (or not)
    AND if (a or b) and c
    c (or not)
    AND if (a or b or c) and d
    ...
    z (or not)

[download]
Here's a sub using a loop: 
$sql_string = make_anded_statement ( username => $form_username,
                                     password => $form_password,
                                     name     => $form_name );

sub make_anded_statement {
  my ( %args ) = @_;
  my @keys = keys %args;
  my $where_string = '';
  my $ANDING = 0;
  foreach my $i ( 0 .. $#keys ) {
    if ( $args{$keys[$i]} ) {
      $where_string .= $keys[$i] . '=' . $args{$keys[$i]} . ' ';
      $ANDING = 1;
    }
    if ( $ANDING && $args{$keys[$i+1]} ) {
      $where_string .= 'AND ';
    }
  }
  return "SELECT FROM table WHERE $where_string";
}

[download]

Replies are listed 'Best First'.
Re: Re: mysql search statement
by Parham (Friar) on Dec 31, 2001 at 19:32 UTC
    i thought i should post an answer even though khkramer had an awesome solution. I got this and thought i should share it :). 
    #!/usr/bin/perl -w 
my $username = 'joejoe'; 
my $password = 'lightning'; 
my $name = 'Joe';
my $SQL = "SELECT FROM table WHERE" .
          (($username)?" username = '$username'":'') .
          (($password)?" and password = '$password'":'') .
          (($name)?" and name = '$name'":'') . ';';
print $SQL;

    [download]
    Happy New Years all :)
[reply]
[d/l]

      So, if $username is not populated (or otherwise false), but password is, you'll get:

      $SQL = "SELECT FROM table WHERE  AND password='pwd'

      [download]

      Just join your WHERE clauses together using the string, ' AND ' as the separator.

      my @WHERE = ();
push @WHERE, "username = '$username'" if $username;
push @WHERE, "password = '$password'" if $password;
push @WHERE, "name = '$name'" if $name;

$SQL = "SELECT FROM table WHERE " . join ' AND ', @WHERE;

      [download]

      Or see my post at 'Re: (2): mysql search statement'.

      dmm

      You can give a man a fish and feed him for a day ...
      Or, you can       teach him to fish and feed him for a lifetime
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom