Find Match In A Set

Say you've been given a list of keywords, and you want to find every news item in the database that matches at least one of those keywords. This problem might occur if someone has selected (or typed in) a series of words and wants to do an "OR" search, that is: find all items that match word-one, or word-two, or word-three, etc.

What you don't want to do is multiple connections and queries to the database, one for each word and then combine the many result sets. SQL provides the answer: IN. Create a set of search words, and then use INto find every item that is in that set.

I like to use placeholders when writing SQL statements, it saves having to worry about quoting and escaping quotes and all that bother. To write the SQL statement in this case requires that we take a bunch of words and turn them into placeholders. So, for example, the string "politics mexico election" would need to be turned into ?, ?, ?. The following code fragment demonstrates how to do the search.

my $string = "politics mexico election";
my @words = split(/ +/, $string);
my $placeholders = join ', ', map{'?'}@words;

my $dbh = DBI->connect('dbi:SQLite:dbname=mydata.db', '', '');
                                
my $sql = qq{
    SELECT news.title FROM news WHERE
    news.keyword IN ($placeholders)
};
my $sth = $dbh->prepare($sql);
$sth->execute(@words);