#
# This code snippet assumes a MySQL database table containing two fields 
# (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 and 
# field2), and/or the sets (beforeMonth, beforeDay, and beforeYear) and 
# (afterMonth, afterDay, and afterYear), which correspond to a value for 
# 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 the 
# 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);
}