Search MySQL

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. I have tried using MATCH
and Fulltext indexing but that doesn't work right either.

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(

Re: Search MySQL

In article <1187202617.542554.275980@50g2000hsm.googlegroups.com>,
wrote:

> 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. I have tried using MATCH
> and Fulltext indexing but that doesn't work right either.
>
> Thanks in advance for any help.

You can split on whitespace:

@words = split " ", $string;

You can find all words (contiguous substrings of 1-9, a-z, A-Z, _):

@words = $string =~ m{ \w+ }gx;

You can find all 5-digit or longer numbers:

@numbers = $string =~ m{ \d{5,} }gx;

What you do with these is then up to you.

--
Jim Gibson

Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com

Re: Search MySQL

On Aug 15, 11:30 am, groups...@gmail.com wrote:
> 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. I have tried using MATCH
> and Fulltext indexing but that doesn't work right either.
>
> 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(
>

Re: Search MySQL

On Wed, 15 Aug 2007 18:30:17 -0000 groups000@gmail.com wrote:

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

At least do s/\W//g to remove all non-word characters, after splitting.
Generally you should treat all external input as dangerous in a CGI
environment; see "perldoc -q cgi":

"How do I make sure users can't enter values into a form that cause my
CGI script to do bad things?

See the security references listed in the CGI Meta FAQ

http://www.perl.org/CGI_MetaFAQ.html"

Here's an implementation of what you're trying to do. I would use
placeholders and a ORM mapper like Rose::DB::Object to achieve this, but
I hope it at least shows you how to use a few Perl techniques.

Ted

#!/usr/bin/perl

use warnings;
use strict;

my $keyword = "hello there;;; you";
my @keywords;
foreach (split ' ', $keyword)
{
s/\W+//g;
push @keywords, $_;
}
print "@keywords\n"; # 'hello', 'there', 'you'

my $st = "SELECT * FROM `productTable` WHERE %s";
my @column_clauses;
foreach my $column (qw/mfg productID desc/)
{
push @column_clauses, join(' OR ', map { sprintf '`%s` LIKE \'%%%s%%\'', $column, $_ } @keywords);
}

$st = sprintf $st, join(' OR ', @column_clauses);

print "$st\n"; # SELECT * FROM `productTable` WHERE `mfg` LIKE '%hello%' OR `mfg` LIKE '%there%' OR `mfg` LIKE '%you%' OR `productID` LIKE '%hello%' OR `productID` LIKE '%there%' OR `productID` LIKE '%you%' OR `desc` LIKE '%hello%' OR `desc` LIKE '%there%' OR `desc` LIKE '%you%'