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

I am looking for some advice on the best way to split up a search field on a web for so I can use the data to search a table in MySQL. Right now the data comes into the script as one string. For example, I have a product in my table that has a Manufacturer of SONY, a Product ID of 12345 and a Description of Color LCD. If my user searches for any of those 3 things individually the product will come up but if the search for SONY Color it will not since it looks for the entire string. Below is the current code I am using. Thanks in advance for any help. 
#!/usr/bin/perl -w

use strict;
use CGI;
use DBI;
use CGI::Carp qw(fatalsToBrowser);

#Create new CGI object
my $cgi = new CGI;

#Connect to DB
my $dbh = DBI->connect("DBI:mysql:XXXXX:XXXXX","XXXXX","XXXXX") or die
$DBI::errstr;

#Print html header
print $cgi->header("text/html");

#pull in keyword from form
my $keyword = $cgi->param("keyword");

#prepare statement
my $sth = $dbh->prepare("SELECT * 
FROM `productTable` 
WHERE `mfg` like '%$keyword%' 
OR `productID` like '%$keyword%' 
OR `desc` like '%$keyword%'") or die;

#execute statement
$sth->execute() or die;

#print results
while (my $rec = $sth->fetchrow_hashref) {
       print qq(
<table>
<tr>
    <td align="left">$rec->{mfg}</td>
    <td align="left">$rec->{productID}</td>
    <td align="left">$rec->{desc}</td>
</tr>
</table>
            );
}

[download]

Replies are listed 'Best First'.
Re: MySQL Keyword Search
by moritz (Cardinal) on Aug 15, 2007 at 20:20 UTC
    If you have a column with a fulltext index, you can use the MySQL syntax 'MATCH(column_name) AGAINST(search_word)'.

    And please use placeholders: 

    my $sth = $dbh->prepare('SELECT * FROM `productTable` WHERE MATCH(`mfg
+`, `productID`, `desc`) AGAINST(?)');
$sth->execute($keywords);
# (untested)

    [download]

    If you don't have a fulltext index, you have to split the search word manually, and construct a corresponding SQL statement.

    Update: BTW if you decorate all your SQL statements with an 'or die', you could alternatively set the RaiseError option during connect(), that way you'll get a better error message, and you'll type less.

    Perl 6 in German

[reply]
[d/l]
[select]
Re: MySQL Keyword Search
by shmem (Chancellor) on Aug 15, 2007 at 20:23 UTC
    From your description it seems like you want the 'keyword' to be broken up at whitespace: 
    my @words = split /\s+/,$keyword;
my @list;
my $where = join(' OR ',
    map { 
        my $col = $_; 
        map { push @list, "%$_%"; "$col like ?" } @words
    } qw(mfg productID desc)
);
my $sth = $dbh->prepare('SELECT * FROM productTable WHERE ' . $where);
$sth->execute(@list);

    [download]

    Note the use of placeholders - the ? signs. Don't use a CGI param directly in a SQL query! *) Run your CGIs with the -T switch and untaint your data (see perlsec).

    *) what happens if the keyword is something like 'foo; drop productTable; select 1 where 0' ?

    --shmem 

    _($_=" "x(1<<5)."?\n".q·/)Oo.  G°\        /
                              /\_¯/(q    /
----------------------------  \__(m.====·.(_("always off the crowd"))."·
");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}
[reply]
[d/l]
      shmem,

      Thank you so much for the quick response. Your solution worked beautifully, it was exactly what I was looking for. I have never used the map function before and am still trying to figure out exactly why it works the way it does. It seems similar to join but with more options. I have so much to learn.

      Regards,
      Rich
[reply]
Re: MySQL Keyword Search
by spatterson (Pilgrim) on Aug 17, 2007 at 11:11 UTC
[reply]

Back to Seekers of Perl Wisdom