On Tue, 05 Nov 2002 08:56:25 -0500 Brad Smith wrote:
I want to run a query that will perform a phrase search on all fields in
the table. I thought that it seemed really logical to write the
statement
like:
my $dbh = DBI->connect('dbi:ODBC:database_name') || die DBI::errstr;
my $sth = $dbh->prepare("SELECT *
FROM table_name
WHERE * LIKE '%$in{'search term'}%'
ORDER BY name
");
$sth->execute();
while (($sid, $name)=$sth->fetchrow_array){
print qq~
....truncated...
~;
}
$dbh->disconnect();
But I am wrong.... miserably, terribly wrong. Perhaps this is off topic,
since it relates more to SQL than DBI, specifically, so please send any
replies directly to mata@matatech.tzo.com. How would one run a
phrase search across all the fields in a table? I am prepared for having
to type 96 OR's if I have to do so, but I am regretting it nonetheless.
I can get it a little on topic by mentioning that there is nothing that
says you have to do all that typing yourself. Perl is very good at
generating the SQL for you.
I generally avoid * even in the select list because you can get bit rather
badly if someone recreates the table for some reason and there are more or
fewer columns or the order of the columns changes.
# Untested, but I've done similar things in other systems
$dbh -> {RaiseError} = 1; # ALWAYS check for errors
# Build list of columns to search
my %bExclude = map { ( lc $_, 1 ) } qw( sid, name );
my $sth = $dbh -> prepare( SELECT * FROM table_name WHERE 1 = 0" );
$sth -> execute();
my @sWhereCols = grep { ! $bExclude{lc $_} } @{$sth -> {NAME}};
$sth -> finish();
# Build where clause and list of values
# You could use a single value if you can use named bind variables,
# but not all databases allow them
my ( @sValue, @sWhere );
foreach ( @sWhereCols ) {
push @sValue, "%" . $phrase . "%";
push @sWhere, " OR $_ LIKE ?";
}
$sWhere[0] =~ s/ OR/WHERE/;
# Search
my ( $iSid, $sName );
$sth = $dbh -> prepare( join "\n",
"SELECT sid, name",
" FROM table_name",
@sWhere,
" ORDER BY name, sid"
);
$sth -> execute( @sValue );
$sth -> bind_columns( \( $iSid, $sName ) );
while ( $sth -> fetch ) {
# Do something with $iSid and $sName
}
