Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
This all started with one of those, "wouldn't it be nice if...?" converstations I often have with clients, the ones where they have a good idea but you really wish they told you before you got 95% of the way though. Anyway, I had a simple search script working, you could select from a couple of combo boxes and type in some text and you were set. The text search was handled pretty simply, same as I normally do. Break up the words and create some horrid SQL that uses like:
@textquery = (); $text =~ s/[^\w\d\s\-]//g; @words = split /\s+/, $text; foreach my $word (@words) { push @textquery, 'search_text LIKE ?'; push @data, '%' . $word . '%'; }
Then it's a pretty simple matter of passing things to DBI and getting some results back. Things worked nicely, the search_text column was a TEXT field that I used CONCAT to fill with the columns that were searchable (name, headline, caption, etc) which is a lot faster than trying to search on multiple columns:
UPDATE table SET search_text = CONCAT_WS(' ', name, headline, caption) +;
</code> Then the client rings. He's been trying to search for 'cat' and 'cup' and not getting the results he wanted. Ok, we'll see what we can do about that. I decide that if the user puts a word in quotes we will search for that exact word.

First we use the ever-so-useful Text::ParseWords to break things up into words and things in quotes:

$text =~ s/[^\w\d\s'"\-]//g; @words = parse_line('\s+', 1, $text);
The 1 means that parse_line will leave the quotes in place, which means we can work out which 'words' if any had quotes around them. However if the user typed in "cat's" we don't get anything back:
unless (@words) { @words = split /\s+/, $text; }
Now we want to do something like /\b$word\b/ in MySQL. Unfortunatly we can't do that with a LIKE statement as we just can't match something at the start or the end of the string with '% word %'. MySQL has some cunning tricks under its sleeves, it supports regexps! These are quite limiting compared to perl but it supports [[:<:]] and [[:>]] which are the same as a \b. The strange thing is that MySQL's regexp's are so slow (I'm running 3.23) that:
SELECT x, y, z FROM table WHERE x REGEXP '[[:<:]]word[[:>]]'
is about 4x as slow (on a table with 5000 rows) as than
SELECT x, y, z FROM table WHERE x LIKE '%word%' AND x REGEXP '[[:<:]]w +ord[[:>]]'
So all we have to do is build up our SQL as before, taking care to use REGEXP if the user put quotes around something:
foreach my $word (@words) { if ($word =~ s/^(["'])(.+)\1$/$2/) { push @textquery, 'srch_text LIKE ? AND srch_text REGEXP ? '; push @data, '%' . $word . '%'; push @data, '[[:<:]]' . $word . '[[:>:]]'; } else { push @textquery, 'srch_text LIKE ?'; push @data, '%' . $word . '%'; } }
And now the user can search for 'cat' and 'cup' to their hearts delight! The client was happy, I learnt a new trick with MySQL and the world continued to spin for a bit longer.

If anyone has any suggestions for improvements, they'd be very much appreciated.


In reply to Searching with MySQL (and REGEXPs) by gav^

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

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (3)
As of 2024-06-21 21:06 GMT
Find Nodes?
    Voting Booth?

    No recent polls found

    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.