OT - Searching databases effectively

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

I appreciate this might be more of an SQL question than a perl one, but I'm hoping I've missed something.

I have a table which looks like this:

CREATE TABLE articles (
  id int(11) NOT NULL default '0',
  title varchar(65) NOT NULL default '',
  bodytext text NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;
[download]

Now I wish to allow users to search these objects in a "typical" fashion.

The naive approach I started with, using DBI, was:

my @terms    = split( / /, $search_terms );

my $querystr = "select id,title,bodytext from articles where ";


my $count = 0;
foreach my $term ( @terms )
{
   if ( $count )
   {
      $querystr .= " or ";
   }
   $querystr .= "bodytext like '%$term%' ";
   $count++;
}

$querystr   .= "order by id desc";

# Do the execution
my $query = $db->prepare( $querystr );
$query->execute();
[download]

This builds up a query string like "select ... from articles where text like %a% or text like %b% or text like %c%".

However this is bad code, it doesnt use parameters like most of my DBI code, and it doesnt allow flexiability. I realise I wish the user to be allowed to search:

For all words
For "Any" words

My SQL-fu is weak, and I see nothing obvious in the DBI docs.

Any assistance is welcome, although I should point out this is just a small part of an application which is working (and designed) pretty well - so I think switching to Class::Dbi is unlikely.

Steve
---
steve.org.uk

Comment on OT - Searching databases effectively Select or Download Code

Replies are listed 'Best First'.
Re: OT - Searching databases effectively by Tanktalus (Canon) on Mar 29, 2005 at 05:54 UTC
How about something like... `my $joiner = $select_any ? ' or ' : ' and '; my $where = join $joiner, ('bodytext like ?') x @terms; $where .= " order by id desc"; my @terms = map { "%$_%" } @terms; my $sth = $db->prepare("$querystr $where"); $query->execute(@terms);` [download] Insert comments and error handling, and this should be much cleaner. Disclaimer: my SQL-fu is weak, too :-)	[reply] [d/l]
Re^2: OT - Searching databases effectively by skx (Parson) on Mar 30, 2005 at 04:54 UTC
That certainly gets rid of the injection problems and uses the placeholders. Steve --- steve.org.uk	[reply]
Re: OT - Searching databases effectively by inman (Curate) on Mar 29, 2005 at 09:19 UTC
If you need to provide a full text search facility then you probably want to implement a search engine. You can then index the text from your database records and search using the full facilities of the search engine rather than SQL. The search results are identified by the SQL primary key so that the result records can be retrieved. Indexing a SQL database is easy to do since you can update the fulltext index from using database triggers (if supported) or frequent scheduled processes. This technique is widely implemented by commercial eCommerce sites. I do this pretty regularly using a large and expensive commercial product but there are numerous open source examples ready to use. Check out the Searchtools.com site for more information.	[reply]
Re^2: OT - Searching databases effectively by skx (Parson) on Mar 30, 2005 at 04:55 UTC
That's probably the best thing to do, although there was a nice link earlier to fulltext searches which should eliminate some of the grunt-work. Thanks. Steve --- steve.org.uk	[reply]
Re: OT - Searching databases effectively by astroboy (Chaplain) on Mar 29, 2005 at 09:35 UTC
Since you're using MySQL's ISAM tables, then you'll be able to use MySQL's built-in full text search engine. You don't want to use "like" when searching for text. Instead, have a read of the FTS doco here	[reply]
Re^2: OT - Searching databases effectively by skx (Parson) on Mar 30, 2005 at 04:55 UTC
This seems like a perfect solution - thanks! Steve --- steve.org.uk	[reply]
Re: OT - Searching databases effectively by naChoZ (Curate) on Mar 29, 2005 at 13:46 UTC
Here's one way I constructed a dynamic query based on the values at hand. Probably not the best way, but it works well for my purposes. # $insert_values = { ...hashref of keys and values... }; # # Break up the hash into separate pieces for columns # and values to use in the sql insert statement. # my @keylist; push @keylist, [ $_, $insert_values->{$_} ] for keys %$insert_values; my ( @columns, @values ); for ( @keylist ) { push @columns, $_->[0]; push @values, $_->[1]; } # # Construct our query based on the lists of columns and # their values. # my $sql = "INSERT INTO table_foo (\n" . join( ", ", @columns ) . "\n) VALUES (\n" . join( ", ", @values ) . "\n)\n"; [download] -- "This alcoholism thing, I think it's just clever propaganda produced by people who want you to buy more bottled water." -- pedestrianwolf	[reply] [d/l]
Re: OT - Searching databases effectively by TedPride (Priest) on Mar 29, 2005 at 17:18 UTC
Wouldn't using placeholders be safer?	[reply]