my $statement1 = "SELECT files FROM catalog WHERE words LIKE %".$word.
+"%";
$sth1 = $dbh -> prepare($statement1);
$sth1 -> execute();
I don't think SELECT files FROM catalog WHERE words LIKE %something% is valid SQL. The %something% needs to be quoted. Which leads me to placeholders.
my $find_files = $dbh->prepare(q{
SELECT files
FROM catalog
WHERE
words LIKE ?
});
for my $w (@words) {
$find_files->execute("%$w%");
while (my ($file) = $find_files->fetchrow_array) {
...
}
}
You run into a similar problem later:
$sth2 = $dbh ->prepare("select filename, type from library
where filename = $file_index
and".$rule_append);
I'm guessing $file_index is a string, not a number, which will lead you to another SQL syntax error. Placeholders are your friends. I would create a hash with the three possible SQL statements, already prepared:
my %search_library = (
email => $dbh->prepare(q{
SELECT filename, type
FROM library
WHERE
filename = ?
AND filetype = 'email'
}),
article => $dbh->prepare(q{
SELECT filename, type
FROM library
WHERE
filename = ?
AND filetype = 'article'
}),
both => $dbh->prepare(q{
SELECT filename, type
FROM library
WHERE
filename = ?
AND (filetype = 'email' OR filetype = 'article')
}),
);
Now you can just do:
for my $w (@words) {
$find_files->execute("%$w%");
while (my ($file) = $find_files->fetchrow_array) {
for my $f (split /:/, $file) {
$search_library{$type}->execute($f);
if (my @rec = $search_library{$type}->fetchrow_array) {
$match{$rec[0]} = $rec[1];
}
}
}
}
Another comment is that I don't know why you're fetching 'filename' from the library table when you're using it to find the columns, unless you're looking to get the normalized value of it (that is, the value as it appears in the database, since your database might be case insensitive).
|