I puzzled with this same issue a few days ago, and received similar advice from a co-worker: put it together as you go. Below is my routine, although I went through and tried to make it more generic (query was for an experimental interface into an internal database used at work). Comments contain more details about what was expected, etc. Still a large chunk of code, if you have lots of fields, but perhaps it might give you an idea. (Comments and suggestions appreciated.)

Update: samgold, my thanks for your catching that bit of code in your response. As I stated, this was code I had attempted to make more generic before posting. In the original code, there were several such sections. That code, as it appears, should rightly have occurred just above the 

$lstatement .= "AND " if ($count);
, as it does in the other if blocks. I commented it out below (rather than removing it, so others could understand the thread), and made the appropriate change. My thanks for catching it. 
#
# This code snippet assumes a MySQL database table containing two fiel
+ds 
# (field1, perhaps a string, and field2, a string representing an IP 
# address) and a timestamp (field3).
#
# The CGI object $q (made available perhaps by "use vars qw($q);") may
+ 
# contain values for param1 and param2 ("like" or "exact" SQL search),
+ 
# param1value and param2value (search values corresponding to field1 a
+nd 
# field2), and/or the sets (beforeMonth, beforeDay, and beforeYear) an
+d 
# (afterMonth, afterDay, and afterYear), which correspond to a value f
+or 
# field3 to before or after, respectively. Values must occur in the 
# particular field for its inclusion in the query. Searches default to
+ 
# "exact" unless "like" is specified. If no values are given, a query 
+to 
# return all values. Results of the query would be ordered first on th
+e 
# timestamp, then on the IP address.
#
# Code would be called as "$statement = &assemble_statement('logtable'
+);", 
# for example.
#
sub assemble_statement {
    my ($table) = @_;

    my %tableinfo = { field1 => param1, field2 => param2 };

    my $lstatement = "SELECT ";
    $lstatement .= "field1, field2, date_format(field3, '%Y-%m-%d %H:%
+i:%S') ";
    $lstatement .= "FROM $table ";
    my $order_by = "ORDER BY field3, inet_aton(field2) ;";

    my $count = 0;
    my ($parameter);

    if (
        (
            defined( $q->param( $tableinfo{"field1"} . "value" ) )
            and ( length( $q->param( $tableinfo{"field1"} . "value" ) 
+) )
        )
        or ( defined( $q->param( $tableinfo{"field2"} . "value" ) )
            and ( length( $q->param( $tableinfo{"field2"} . "value" ) 
+) ) )
      )
    {
        #
        # Commented out and corrected thanks to comment
        #   from samgold
        #
        # $lstatement .= "WHERE " unless ($count);
        #

        foreach my $k ( keys(%tableinfo) ) {
            if ( ( defined( $q->param( $tableinfo{$k} ) ) )
                and ( length( $q->param( $tableinfo{$k} ) ) ) )
            {
                #
                # "WHERE" line added to fix issue pointed
                #   out by samgold
                #
                $lstatement .= "WHERE " unless ($count);
                $lstatement .= "AND " if ($count);
                $lstatement .= "($k ";
                if ( $q->param( $tableinfo{$k} ) eq "like" ) {
                    $lstatement .= "LIKE ";
                }
                else {
                    $lstatement .= "= ";
                }
                $parameter = $q->param( $tableinfo{"field1"} );
                foreach ( 0 .. 1 ) {
                    $parameter = reverse($parameter);
                    $parameter =~ s/\s+$//g;
                }
                $lstatement .= "\"" . $parameter . "\") ";
                $count++;
            }
        }
    }

    if (
        (
            (
                defined( $q->param("beforeMonth") )
                and ( length( $q->param("beforeMonth") ) )
            )
        )
        and (
            (
                defined( $q->param("beforeDay") )
                and ( length( $q->param("beforeDay") ) )
            )
        )
        and (
            (
                defined( $q->param("beforeYear") )
                and ( length( $q->param("beforeYear") ) )
            )
        )
      )
    {

        $lstatement .= "WHERE " unless ($count);
        $lstatement .= "AND " if ($count);

        $lstatement .= sprintf(
            "(TO_DAYS(field3) <= TO_DAYS('%04d-%02d-%02d')) ",
            $q->param("beforeYear"),
            $q->param("beforeMonth"),
            $q->param("beforeDay")
        );
    }
    if (
        (
            (
                defined( $q->param("afterMonth") )
                and ( length( $q->param("afterMonth") ) )
            )
        )
        and (
            (
                defined( $q->param("afterDay") )
                and ( length( $q->param("afterDay") ) )
            )
        )
        and (
            (
                defined( $q->param("afterYear") )
                and ( length( $q->param("afterYear") ) )
            )
        )
      )
    {

        $lstatement .= "WHERE " unless ($count);
        $lstatement .= "AND " if ($count);

        $lstatement .= sprintf(
            "(TO_DAYS('%04d-%02d-%02d') <= TO_DAYS(field3)) ",
            $q->param("afterYear"),
            $q->param("afterMonth"),
            $q->param("afterDay")
        );
    }

    $lstatement .= $order_by;
    return ($lstatement);
}

[download]

In reply to Re: SQL parser from fragments by atcroft
in thread SQL parser from fragments by snapdragon

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.