You occasionally have to write different SQL depending on the situation. I use the following code to basically build up the SQL query, and keep track of the values I'm going to need to bind. (it's a little more abstracted than you probably need -- I have multiple search engines that use this module):

sub _AddSQL {
        my ($self, $sql, @bindvars) = @_;
        
        push @{$self->{'where'}}, $sql;
        push @{$self->{'bind'}}, @bindvars;
}
...
sub _SendSearch {
        my $self = shift;

        my $dp = $self->dataprovider();

        my $db = $dp->GetDatabase() or
                return $self->_ThrowError('500 Database unavailable');

        my $where = 'AND  ('. join ( ') and (', @{$self->{'where'}}). 
+') ';

...
        my $sql = $dp->sql_prefix() . $where . $dp->sql_suffix();
        
        $sth = $db->prepare( $sql )
                or return $self->_ThrowError ( '500 Cannot prepare sea
+rch', debug => $db->errstr );
        $sth->execute( @{$self->{'bind'}} )
                or return $self->_ThrowError ( '500 Cannot execute sea
+rch', debug => $db->errstr );

        my %fields;
        $sth->bind_columns( \(@fields{ @{ $sth->{'NAME_lc'} } } ) );
        
        my @records = ();
        
        while ( $sth->fetch() ) {
                push @records, $self->_ProcessRecord( \%fields );
        }
        
        return $self->dataprovider()->_PackageResults ( $numFound, \@r
+ecords, debug => $where );
}

[download]

The code that inherits it defines a series of handlers for different search terms, eg:

sub _ProcessParam_time {
        my ($self, $time) = @_;

        $self->_AddSQL( 's.date_obs BETWEEN ? AND ?',
                $self->format_date( $time->{'start'} ),
                $self->format_date( $time->{'end'},1 ),
        );
        return;
}
...
sub _ProcessParam_nickname {
        my ($self, $nicknames) = @_;

        if (!ref($nicknames)) { $nicknames = [$nicknames] } # deal wit
+h scalar values (should be an array)
        
        my %nicks = map { $_ => undef } @$nicknames;
        
        if ( %nicks ) {
                my %obsdirs = map { $_ => 1 } qw( FG FGSIQUV SP4D FGNG
+ FGDG FGIV FGFOCUS FGSIV100 FGSIV200 FGSIV CL FGIQUV );
                my @obs = grep { $obsdirs{$_} } @$nicknames;
                                
                if ( @obs ) {
                        $self->_AddSQL( 'obsdir IN ('.join(',',('?')x@
+obs).')', @obs );
                        delete @nicks{@obs};
                }
                
                # everything else, we try to match against 'wave'.
                $self->_AddSQL(
                        '('.join(' OR ', ('wave LIKE ?') x (keys %nick
+s)).')',
                        map { "%$_%" } ( keys %nicks )
                );
        }

        return;
}
...
sub _ProcessParam_wave {
        my ($self, $wave) = @_;
        
        if (defined($wave->{'wavetype'})) {
               # return $self->_ProcessParam_wave_type( $wave->{'wavet
+ype'} );
               $self->_ProcessParam_wave_type( $wave->{'wavetype'} );
        }

        # force the wave to Angstrom
        $wave = $self->_WaveConversion($wave)
                unless $wave->{'waveunit'} eq 'Angstrom';

        if ($wave->{'waveunit'} ne 'Angstrom') {
                # conversion to Angstrom failed for some reason
        
                return $self->_PackageResults ( undef, [], debug => 'u
+nknown waveunit' );
        }

        if (defined($wave->{'wavemin'})) {
                $self->_AddSQL( 'l.wavemax >= ?',
                        $wave->{'wavemin'},
                );
        }
        
        if (defined($wave->{'wavemax'})) {
                $self->_AddSQL( 'l.wavemin <= ?',
                        $wave->{'wavemax'},
                );
        }
        return;
}

[download]

(and crap -- would you believe, by posting this, I noticed a logic error in the code ... if 'wavetype' was defined, I wasn't processing the range match)

In reply to Re: Wildcard variable bind? by jhourcle
in thread Wildcard variable bind? by Anonymous Monk

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.