Re: mysql search statement

[khkramer]

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" : '' );

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)

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";
}

[Parham]

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;

Happy New Years all :)

[dmmiller2k]

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

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

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;

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