As I need to read line by line and search them in the database, I used a subroutine to handle all database work. Below is my code.
sub search{
#take search parameter from html <form/>
my $q = $_[0]; #taking param;
my $found = 0; #initialize category found count;
#open database
my $ref = {RaiseError => 1, AutoCommit => 0};
my $dbh = DBI->connect('DBI:mysql:diet', $ref) || die "Failed to connect: $DBI::errstr";
#prepare SQL search statement
my $sql = qq{select topic FROM table1 WHERE uri LIKE '$q'};
my $sth = $dbh->prepare($sql);
$sth->execute();
while(my $record = $sth->fetchrow_hashref) {
no warnings;
print &topic($record->{topic}), "
\n";
$found++;
}
if ($found == 0){ #if no category was found, output no found
print "
$q is not found in the database!
\n";
}
$sth->finish();
$dbh->disconnect(); #disconnect from database;
}