Search MySQL

G

groups000

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(
<table>
<tr>
<td align="left">$rec->{mfg}</td>
<td align="left">$rec->{productID}</td>
<td align="left">$rec->{desc}</td>
</tr>
</table>
);
}
 
R

Ron Bergin

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(
<table>
<tr>
<td align="left">$rec->{mfg}</td>
<td align="left">$rec->{productID}</td>
<td align="left">$rec->{desc}</td>
</tr>
</table>
);

}

It would be much better to have 3 or more form fields (brand,
part_number, description, etc) instead of lumping them into 1 var.
 
T

Ted Zlatanov

On Wed, 15 Aug 2007 18:30:17 -0000 (e-mail address removed) 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%'
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top