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:

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

In reply to OT - Searching databases effectively by skx

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.