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>
);
}
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>
);
}