Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: A Case with 5 Var's

by ultibuzz (Monk)
on Jan 25, 2007 at 12:18 UTC ( #596458=note: print w/replies, xml ) Need Help??

in reply to A Case with 5 Var's

wow great tips, and now what i want to do.
i need to define a qry wich depends on the var's and is different for any possibility.
can this be done with a hash ?
no text file processing or so, var's are filled from keyb
kd ultibuzz

Replies are listed 'Best First'.
Re^2: A Case with 5 Var's
by Corion (Pope) on Jan 25, 2007 at 12:29 UTC

    What kind of query? If you want to generate an SQL query from a hash, there is SQL::Abstract, which does just that:

    use strict; use SQL::Abstract; my $sql = SQL::Abstract->new; my $table = 'customers'; my @fields = qw(name vorname plz tel); # You shouldn't have $name, $vorname etc. but a hash # containing the things you want: my %where = ( name => $name, vorname => $vorname, ); my ($stmt, @bind) = $sql->select($table, \@fields, \%where); print <<EOM; -- I use the following SQL: $stmt -- with the following placeholders @bind

      its an qry for a local unique system wich is not public, it needs defined stuff so i need to check if a var is filled or not so i can create a exact matching qry

      example: only $name and $plz is filled with Mueller and 55555 both are strings btw, then the qry looks like
      for each possibility is another qry defined ;/
      kd ultibuzz

        It depends how your query is built. Hopefully each variable is responsible for just one section, then you just build the query up as you go.

        my $qry = ""; my $name = "Mueller"; my $plz = "55555"; if ($name) { $qry .= "::5544$nameGDFT"; } if ($plz) { $qry .= "xxxxx$plz::0:";} print $qry;

        Since I have no idea what kind of query that is I had no idea what parts go where, but hopefully that gives you and idea how it can be done.

        If your query is completly dependent on the elements available then you could combine your ifs together some for readability at least

        if ($name && $vorname && $plz && $tel && tel49) { $qry = "whatever +" } elsif ($name && $vorname && $plz && $tel) { $qry = "whatever +2" } elsif ($name && $vorname && $plz) { $qry = "whatever +3" };

        That is not the recommended solution, just one of the many ways to do it.

        Eric Hodges
Re^2: A Case with 5 Var's
by davorg (Chancellor) on Jan 25, 2007 at 12:36 UTC

    I've no idea what kind of "qry" you're talking about. But let's assume you want a database query where the contents of the where clause are controlled by the existance of data items that are stored in a hash. The keys of the hash are the column names and the values are the values that are required.

    In the past I've used code something like this:

    my $sql = 'select col_x, col_x from a_table'; # You'll be setting this hash up from some kind of # input to your program. I'm using variables for # illustrative purposes. my %cols = ( col_x => $foo, col_y => $bar, col_z => $baz, ); my @where; my @vals; foreach (keys $cols) { if (defined $cols{$_}) { push @where, "$_ = ?"; push @vals, $cols{$_}; } } if (@where) { $sql .= ' where ' . join(' and ', @where); } my $sth = $dbh->prepare($sql); $sth->execute(@vals);

    Update: Or use SQL::Abstract as Corion points out.

      i'm testing a combined hash and bit solution wich untill now looks great,
      thx for the great help and examples
      kd ultibuzz

        If your query consists of embedding the available data into a string in a specific order, you could just interpolate it:
        my $query = ":$name:$vorname:$plz:$tel:$tel49:";
        If the query structure needs to look different for each combination of data, you could use the bit vector you're constructing to access a hash:
        my %query_for = ( 0 => "::", 1 => "name IS \$name", 2 => "vorname IS \$name", ... );
        You'd need 2^5 (32) entries in the hash if every possible combination of the five items was valid; if not, you could leave out any invalid combinations. This means that if $query_for{$bit_vector} was undef, you'd know you had an invalid combination without any comparison logic at all.

        Note that I escaped the "query" strings; that way, you can do an eval on the string and interpolate the variables into the query at the time you have the values you want. If the variables weren't escaped, whatever values they had (probably undef) at the time the hash was constructed would be substituted in immediately, and you'd always get queries without any data in them.

        A much more advanced way to do it is to use anonymous subroutines as the entries in the hash:

        my %query_constuctor_for = ( ... 18 => sub { return ":NIL/$vorname::$tel49:" }, 19 => sub { return ":$name/$vorname::$tel49:" }, ... );
        Lookup works the same way, but you'd say
        my $query_maker = $query_constructor_for{$bitvector}; defined $query_maker or die "Combination invalid"; $query = $query_maker->()
        to get your query; the $query_maker->() expression calls the anonymous subroutine that you looked up with $query_constructor_for{$bitvector}. This lets you construct arbitrarily-complicated code to do what's needed for each combination.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://596458]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (3)
As of 2018-01-20 19:55 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (227 votes). Check out past polls.