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

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.