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

In reply to generating various sql arguments dynamically, and on crack by mr.dunstan

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.