mr.dunstan has asked for the wisdom of the Perl Monks concerning the following question:

I am building a rather complex search interface for a web app. My task is to dynamically construct a query in my perl, based on which options the user checks. In this instance, I want to give the user the possibility to choose any number of criteria for their search. In this example, I would like the user to pick one, many or all of the 'status types' for lack of a better description, like so: 
<input type=checkbox name=col1 value=1>NEW
<input type=checkbox name=col2 value=4>PENDING
<input type=checkbox name=col3 value=9>CLOSED

[download]
## say I magically have a hash filled with the name value pairs from the form input and I add up the values of the 3 fields, and I get: 
$sum = $form{'col1'} + $form{'col2'} + $form{'col3'};

[download]
Then from this 'bitmask' I can figure out which criteria to add to my query, using a hash like so ... 

%possibleSQLargs = {
        1 => "and table.column = 'NEW'";
        4 => "and table.column = 'PND'";
        9 => "and table.column = 'CLS'";
        5 => "and (table.column = 'NEW' or table.column = 'PND')";
        10 => "and (table.column = 'NEW' or table.column = 'CLS')";
        13 => "and (table.column = 'PND' or table.column = 'CLS')";
        15 => "and (table.column = 'NEW' or table.column = 'PND' or ta
+ble.column = 'CLS')";
    };

$extraqueryarg = $possibleSQLargs{'$sum'};

[download]
... and voila, now I have my extra sql argument to tack onto my query.

My question: have I been smoking perl/sql crack? I have a feeling there might be a better way, but I think this is simple for when the user can only pick a few different options ... I'm certain this wouldn't scale if the user can pick more than 3 or 4 options, but this might be a good start. Does anyone out there know a better way to do this, and if you do - would you share?! Please!


humbly,
-mr.dunstan

Edit: chipmunk 2001-06-27

Replies are listed 'Best First'.
Re: generating various sql arguments dynamically, and bon crack/b
by chromatic (Archbishop) on Jun 28, 2001 at 03:04 UTC
    MySQL, at least, supports the IN() clause. For example, I just ran a query: 
    select title from node where node_id in (1, 2, 3);

+----------+
| title    |
+----------+
| nodetype |
| node     |
| setting  |
+----------+
3 rows in set (0.05 sec)

    [download]
    If your database supports that or something similar, you can build a clause resembling and table.column in ('PND', 'NEW'). 
    my %possibleArgs = (
    1 => 'NEW',
    4 => 'PND',
    9 => 'CLS',
);

my @args;
for ('col1', 'col2', 'col3') {
    if (exists($possibleArgs{$_})) {
        push @args, $possibleArgs{$_};
    }
}
$extraqueryarg = "and table.column in (" . join(',', @args) . ")";

    [download]
    Something similar to that would do the trick.
[reply]
[d/l]
[select]
      Gosh, thanks!

      I had completely forgotten about IN() ... thank you perl monks! Good karma and good health!

      -mr.dunstan
[reply]
Re: generating various sql arguments dynamically, and bon crack/b
by runrig (Abbot) on Jun 28, 2001 at 03:03 UTC
    Name all the fields with the same name, then just join an array of (valid) values. Oh yeah, and use placeholders (here's a rough sketch):
    $form{col} = [ qw(NEW PND CLS) ];
my $query_arg = join(" or ", "table.column=?" x @$form{col});
# Assuming you have main query
$query_str .= $query_arg if $query_arg;
...
$sth->execute(@$form{col});

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom