Hey Mike,
>
> Basically what I have is a SELECT query I need to generate on the fly to
> construct the search on the keywords entered into the form: $FORM{'words'}
> The query looks something like this:
>
> SELECT * FROM bus_info
> WHERE searchwords LIKE "%$word%";
>
> my @words = split(/ /,$FORM{'words});
>
> for my $word (@words) {
> # do some regrex here to clean up each $word
>
> }
Why do you want to do this on the fly, as the result is pretty homogene
as I can imagine ?
It would be better to put all the keywords in one query and only execute
once or lets say building the query on the fly. This also allows you to
insert logical operators in your query like AND LIKE or OR LIKE or AND
NOT LIKE and so on (use radio buttons in your form so you wont have to
catch input errors for that). Building your query on the fly would
implicitely mean you use a logical OR which is not always going to
deliver satisfying results (too many hits).
# get the data from the form
my @words = split(/ /,$FORM{'words});
foreach $word (@words) {
# do some cleaning as you wish
}
# get AND, OR , AND NOT, ...
my $op = $FORM{'boolean_op'};
# start building the query
my $sql = "SELECT * FROM table where column LIKE '%" . pop(@words) .
"%'";
# any words left in the list ?
foreach $word (@words) {
# LIKE is redundant so put it here
$sql .= " $op LIKE '%" . $word . "%'";
}
# $sql now basically looks like this If you used AND LIKE as logical op:
# "select * from table where column LIKE '%word1%' AND LIKE '%word2%'"
# prepare and execute
my $sth=$dbh->prepare($sql);
$sth->execute();
# fetch or equivalent
You can also implement a respective boolean operator for every keyword.
PS: please excuse any typos, and syntax errors. code is not tested
Martin
--
Martin Hesse [EMAIL PROTECTED]
nova ratio AG
Ransbach-Baumbach
Germany
[EMAIL PROTECTED]
Tel. : 02623 92420
Fax. : 02623 9242100
The information contained in this e-mail and any attachments is strictly
confidential and may be legally privileged. It is intended solely for
the use of the individual or entity to whom it is addressed. If you are
not the named addressee, you are hereby notified that it is prohibited
and may be unlawful to disclose, copy, distribute, store the information
in any medium or usage for another purpose.
