Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Greetings O' Wise Ones

I'm trying to search a database field (of type TEXT) containing a description of certain tropical plants using a number of keywords.

I'm allowing the user to type in certain keys words (e.g. "red flower october") into a field, an want to return all records whos description contains one or all of the key words.

loop within a loop?

I'm not sure whether to loop through each keyword (i.e. "red" then "flower" then "october") searching for it in the description then return the result and move to the next record.

Any tips would be much appreciated.

I've tried :- 
$sql_stmt = "select * from database where ";
foreach $keyword ( @keywords )
{
  $sql_stmt .= " AND ( PlantDescription REGEXP "s/\$keyword/" );"
}

[download]
returning multiple records that match the $keyword's criteria.

Is there a better way to do this using regexes?

Thanks

MD

20030709 Edit by Corion: Fixed formatting, added code tags

Replies are listed 'Best First'.
Re: regex search
by antirice (Priest) on Jul 09, 2003 at 09:22 UTC

    If you're just trying to see whether or not any of the words are contained within the text field, try using LIKE instead of REGEXP. As for the SQL statement, if you're using DBI then please use placeholders.

    my $dbh = makeDBConnectionSomehow;
my @keywords = qw/blah bleh blah/;

# formats each keyword so it looks like %keyword%
my @executeKeywords = map ("\%$_\%",@keywords);
my $sql_stmt = "select * from database where";

# puts the "plantdescription like ? or pla.." at end of sql_stmt...one
+ like per keyword
$sql_stmt .= join(" OR",(" PlantDescription LIKE ?")x@keywords); 

my $query = $dbh->prepare($sql_stmt);
my $query->execute(@executeKeywords);

    [download]

    Of course, I have a few links to recommended reading: Tricks with DBI, DBI pod, MySQL documentation on LIKE, and the DBI::FAQ.

    antirice    
    The first rule of Perl club is - use Perl
    The     ith rule of Perl club is - follow rule i - 1 for i > 1

[reply]
[d/l]
      Thanks Antirice.

      I'll have a play around with the code you've provided and bookmark those links after I've check 'em out.

      Cheers
      MD
[reply]
Re: regex search
by Lachesis (Friar) on Jul 09, 2003 at 09:30 UTC
    As it is your code will give an error because of the unterminated substitution. It won't create valid sql and would actually do an all keywords match only. you want to try something along the lines of 
    $sql_stmt = "select * from database where 1=1";
#This is how I would do the match in Postgresql
#Check your DB docs for the correct syntax
my $matchstring  = join ' OR ', map { "PlantDescription ~ ".$dbh->quot
+e($_)} @keywords;
$sql_stmt .= ' AND '.$matchstring if $matchstring;

    [download]
[reply]
[d/l]
Re: regex search
by roju (Friar) on Jul 09, 2003 at 20:23 UTC
    I'm not familiar with using regexp's in SQL, but could you not join all the keywords into one regex? Ie, instead of doing 
    foreach $keyword ( @keywords )
{
  $sql_stmt .= " AND ( PlantDescription REGEXP "s/\$keyword/" );"
}

    [download]
    you could do 
    $keywords = join "|", @keywords;
$sql_stmt .= "AND ( PlantDescription REGEXP \"$keywords\" )";

    [download]
[reply]
[d/l]
[select]
Re: regex search
by dvergin (Monsignor) on Jul 10, 2003 at 00:15 UTC
    You don't say which database you are using. But MySQL and other databases provide a Full-text Search capability which provides weighted results to text queries of just the kind you are describing. You can access this through DBI just like any other SQL query you might submit.

    There is some overhead involved for the database engine to maintain the full-text index, but I have found that to be small in comparison to the savings, flexibility, and convenience in searching -- especially when the data is relatively stable in comparison to the frequency of the searches.

    ------------------------------------------------------------
    "Perl is a mess and that's good because the
    problem space is also a mess.    " - Larry Wall

[reply]

Back to Seekers of Perl Wisdom