while(@row = $sth->fetchrow_array){ 
(how do I send results to the template from here)... 
}

my @search_results = search_abook($form{searchfield}, $form{searchvalue}); 
 
$template->param(results => \@search_results); 
 
print $template->output; 
 
#-------------- 
 
sub search_abook { 
   my ($field, $search_pattern) = @_; 
 
# stripped code that sets up the db connection and sql statement 
 
   if ($sth->rows > 0) { 
      while (my ($owner, $full, $first, $last, $email, $label) = $sth->fetchrow_array) { 
         my %account = ( 
                        owner       => $owner, 
                        fullname    => $full, 
                        firstname   => $first, 
                        lastname    => $last, 
                        email       => $email, 
                        label       => $label, 
         ); 
         push @accounts, \%account; 
      } 
   } 
   $dbh->disconnect; 
   return @accounts; 
}

    <tmpl_loop name="results"><tr> 
      <td><tmpl_var name="owner"></td> 
      <td><tmpl_var name="fullname"></td> 
      <td><tmpl_var name="firstname"></td> 
      <td><tmpl_var name="lastname"></td> 
      <td><tmpl_var name="email"></td> 
      <td><tmpl_var name="label"></td> 
      <tmpl_if NAME="gic"> 
        <td><a href="modify.pl?locate=1&amp;id=<tmpl_var name="owner">">mod</a></td> 
        <td><a href="delete.pl?locate=1&amp;id=<tmpl_var name="owner">">del</a></td> 
      </tmpl_if> 
    </tr> 
    </tmpl_loop>

my $accounts = $sth->fetchall_arrayref( { } );

my $sql; 
if ($search_type eq 'Title'){ 
     $sql = "SELECT * FROM dvd WHERE Title Like ? and Format=?"; 
}

my $sql;  
if ($search_type eq 'Title'){  
     $sql = "SELECT * FROM dvd WHERE Title Like ? and Format=?";  
} 
 
my $sth = $dbh->prepare($sql); 
$sth->execute('%value%', 'my_format');

package MyApp::User; 
use strict; 
use warnings; 
 
use base 'CGI::Application'; 
use CGI::Application::Plugin::DBH        (qw/dbh_config dbh/); 
use CGI::Application::Plugin::ConfigAuto (qw/cfg cfg_file/); 
use DBI; 
use Data::Dumper; 
 
#------------------------------------------------------------------------------ 
# Define our run modes 
#------------------------------------------------------------------------------ 
sub setup { 
    my $self = shift; 
    $self->start_mode('search_DB'); 
    $self->error_mode('error'); 
    $self->mode_param('rm'); 
    $self->run_modes( 
        'search_DB' => 'search_DB', 
        'display_results'     => 'display_results', 
    ); 
} 
 
#------------------------------------------------------------------------------ 
# Process any fatal errors 
#------------------------------------------------------------------------------ 
sub error { 
    my $self  = shift; 
    my $error = shift; 
    return "There has been an error: $error"; 
} 
 
sub database{ 
	my $self = shift; 
	my $search_type = shift; 
	my $media_format = shift;  
	$media_format = "DVD/Blu-Ray" if($media_format eq 'Both'); 
	my ($search_string) = shift; 
	 
    my @flicks; 
	my $dbh = DBI->connect('dbi:mysql:movies','mvdb','garbage') or die "Connection Error: $DBI::errstr\n"; 
 
	my $sql; 
	if ($search_type eq 'Title'){ 
		$sql = "SELECT * FROM dvd WHERE Title LIKE % ? % and Format=?"; 
	} 
	elsif ($search_type eq 'Year'){ 
		$sql = "SELECT * FROM dvd WHERE year = ? AND Format=?"; 
	} 
	elsif ($search_type eq 'Genre'){ 
		$sql = "SELECT * FROM dvd WHERE Genre LIKE ? AND Format=?"; 
	} 
	elsif ($search_type eq 'Keywords'){ 
		$sql = "SELECT * FROM dvd WHERE Keywords LIKE ? AND Format=?"; 
	} 
 
 
	my $sth = $dbh->prepare($sql) or die "Couldnt prepare statement: $DBI::errstr \n"; 
	$sth->execute(%$search_string%,$media_format) or die "SQL Error: $DBI::errstr \n"; 
	 
	if ($sth->rows > 0){ 
		while (my ($number, $title, $year, $genre, $keywords, $format, $imdb, $notes, $double) = $sth->fetchrow_array){ 
			my %flick = ( 
						number		=> $number, 
						title		=> $title, 
						year		=> $year, 
						genre		=> $genre, 
						keywords	=> $keywords, 
						format		=> $format, 
						imdb		=> $imdb, 
						notes		=> $notes, 
						double		=> $double, 
						); 
			push @flicks, \%flick; 
		} 
	} 
	else{ 
			my %flick = ( 
						number		=> 'not found', 
						title		=> 'not found', 
						year		=> 'not found', 
						genre		=> 'not found', 
						keywords	=> 'not found', 
						format		=> 'not found', 
						imdb		=> 'not found', 
						notes		=> 'not found', 
						double		=> 'not found', 
						); 
		push @flicks, \%flick; 
	} 
 
	$dbh->disconnect; 
	return @flicks; 
} 
 
#------------------------------------------------------------------------------ 
# Display the form for creating/maintaining users 
#------------------------------------------------------------------------------ 
sub search_DB { 
    my $self = shift; 
    my $errs = shift; 
    my $q    = $self->query; 
 
    # Process the template 
    my $template = $self->load_tmpl('searchDB.tmpl'); 
	my $query = $self->query(); 
 
	$template->param('showsearch_page' => 1 ); 
	 
    $template->param($errs) if $errs; 
 
    #Display the form 
    return $template->output(); 
} 
 
sub display_results{ 
	my $self = shift; 
    my $errs = shift; 
	my $query = $self->query(); 
	my $search = $query->param('query'); 
	my $search_type = $query->param('type'); 
	my $media_format = $query->param('format'); 
 
    my $template = $self->load_tmpl('searchDB.tmpl'); 
 
	my @results = $self->database($search_type, $media_format, $search); 
	$template->param(showresults_page => \@results); 
 
 
#	$template->param(format => $format); 
	return $template->output; 
} 
 
1;

$sql = "SELECT * FROM dvd WHERE Title LIKE % ? % and Format=?";  
$sth->execute(%$search_string%,$media_format) or die "SQL Error: $DBI::errstr \n";

	my $sql;  
	if ($search_type eq 'Title'){ 
	        $search_string = '%' . $search_string . '%'; 
		$sql = "SELECT * FROM dvd WHERE Title LIKE ? and Format=?";  
	}  
	elsif ($search_type eq 'Year'){  
		$sql = "SELECT * FROM dvd WHERE year = ? AND Format=?";  
	}  
	elsif ($search_type eq 'Genre'){  
		$sql = "SELECT * FROM dvd WHERE Genre LIKE ? AND Format=?";  
	}  
	elsif ($search_type eq 'Keywords'){  
		$sql = "SELECT * FROM dvd WHERE Keywords LIKE ? AND Format=?";  
	}  
  
  
	my $sth = $dbh->prepare($sql) or die "Couldnt prepare statement: $DBI::errstr \n";  
	$sth->execute($search_string, $media_format) or die "SQL Error: $DBI::errstr \n";