Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
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 avoiding work at the Monastery: (6)
As of 2014-11-26 23:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (177 votes), past polls