Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

if/else options

by imrags (Monk)
on Aug 20, 2009 at 11:22 UTC ( #790061=perlquestion: print w/ replies, xml ) Need Help??
imrags has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks
I have 3 different variables. I try to create a query based on the existence of 1,2 or all 3 of the variables.
Example:
select * from <table> where colum1="$var1" and column2="$var2" and col +umn3="$var3";
However, since it is not necessary that all 3 are defined, there are many
combinations possible ($var1 exists, others don't etc.)
It depends on user as to how many variables he is looking for
Is there a way I can create a single query which takes care of existence of the three variables
without me having to use 6 if/else statements?
Thanks for the help,
Raghu

Comment on if/else options
Download Code
Re: if/else options
by busunsl (Vicar) on Aug 20, 2009 at 11:37 UTC
    Something like
    my @where; my $sql = 'select * from table'; push @where, 'column1 = "' . $var1 . '" ' if defined $var1; push @where, 'column2 = "' . $var2 . '" ' if defined $var2; push @where, 'column3 = "' . $var3 . '" ' if defined $var3; $sql = $sql . ' where ' . join(' and ', @where);
    would do the trick.

    When you put the values into an array, it would be even better:

    my @values = (undef, $var1, $var2, $var3); my @where; my $sql = 'select * from table'; foreach my idx (1..3) { push @where, qq(column$dx = "$values[$idx]") if defined $values[$idx +]; } $sql = $sql . ' where ' . join(' and ', @where);
      ... and don't forget to handle the case where no values have been supplied (return an error or all results). e.g.
      $sql = $sql . ' where ' . join(' and ', @where) if defined $where[0];
      map{$a=1-$_/10;map{$d=$a;$e=$b=$_/20-2;map{($d,$e)=(2*$d*$e+$a,$e**2 -$d**2+$b);$c=$d**2+$e**2>4?$d=8:_}1..50;print$c}0..59;print$/}0..20
      Tom Melly, pm (at) cursingmaggot (stop) co (stop) uk
      Except, of course, that interpolating user-supplied input directly into your SQL statements is bad ju-ju. Much better would be:
      my @where; my @values; my $sql = 'select * from table'; if defined $var1 { push @where, 'column1 = ?'; push @values, $var1; } if defined $var2 { push @where, 'column2 = ?'; push @values, $var2; } if defined $var3 { push @where, 'column3 = ?'; push @values, $var3; } $sql = $sql . ' where ' . join(' and ', @where); my $sth = $dbh->prepare_cached($sql); $sth->execute(@values);
      or
      my @values = (undef, $var1, $var2, $var3); my @where; my $sql = 'select * from table'; my @defined_values; foreach my idx (1..3) { if defined $values[$idx] { push @where, qq(column$dx = ?); push @defined_values, $values[$idx]; } } $sql = $sql . ' where ' . join(' and ', @where); my $sth = $dbh->prepare_cached($sql); $sth->execute(@defined_values);
Re: if/else options
by JavaFan (Canon) on Aug 20, 2009 at 11:52 UTC
    my @clauses; push @clauses, 'column1' if defined $var1; push @clauses, 'column2' if defined $var2; push @clauses, 'column3' if defined $var3; my $query = "select * from table"; if (@clauses) { $query .= " where " . join ' and ', map {"$_ = ?"} @clauses } my $result = $dbi->selectall_arrayref($query, {}, grep {defined} $var1 +, $var2, $var3);
Re: if/else options
by imrags (Monk) on Aug 20, 2009 at 11:56 UTC
    Thank you so much... I am sure your replies will solve the problem...
    Raghu
Re: if/else options
by youlose (Scribe) on Aug 20, 2009 at 19:00 UTC
    see Sql::Abstract that is what you need

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://790061]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (6)
As of 2015-07-05 10:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (61 votes), past polls