List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 16 1999 3:10pm
Subject:Re: Problem with Mysql and Perl
View as plain text  
On Mon, 1999-08-16 11:05:45 +0300, Deniz Demir wrote:

[Perl problem]

> @keyN  = split('+', $value);
> 
> but it cause an internal error, and I looket at server error log and It
> says like this:
> 
> /+/ ?*{} follows not an operator (Mybe I can not remember exactly but
> almost like)

Have a look at the description of split, using: man perlfunc

The Perl function split wants a regular expression as delimiter, so to
use a verbose plus sign one must write: split(/\+/, $value)

(You have to learn a little bit about Perl regular expression to get
 the full picture ... But it's worth the effort, because regexps are
 _the_ Swiss Knife of text processing!)


[MySQL task:  searching a field for several values]

> @keyN = split(' ', $value);
> 
> $statement = "select * FROM cds WHERE productName LIKE '%@keyN%' AND
> language LIKE '%$keywordsLanguage%'";
> 
> I want to separate the value with respect to space and to store each
> separated word into @keyN, associative array. In the second statement, I
> want to search my table with column of productName with using this
> associative array, keyN. I want the records which contain the all words
> in this associative array.

There's no special function or operator in SQL to do so, though in
MySQL, there's REGEXP in addition to the standard LIKE.
  LIKE  : pattern matching using the SQL jokers: _ %
  REGEXP: pattern matching using regular expressions (POSIX style,
          not Perl style with its own extensions)
For looking up a string inside another string, there's also
  LOCATE(substr,str)  and equivalent  INSTR(str,substr)


So there are several ways to accomplish your task:

a) Using a sequence of LIKE terms.
   
   The resulting SQL statement will look something like that:
    SELECT *
    FROM cds
    WHERE  productName LIKE '%mike%'
       AND productName LIKE '%oldfield%'
       AND productName LIKE '%word3%'
   [...]
       AND language LIKE '%en%';

   To construct this SQL query in Perl, for example you could write:
   | $statement = "SELECT * FROM cds WHERE ";
   | foreach $word (@keyN) {
   |    $statement .= "productName LIKE '%$word%' AND ";
   | }
   | $statement .= "language LIKE '%$keywordsLanguage%'";

b) Using a sequence of simple REGEXP terms.
   (probably slower than (a), but worth a try).

   Just replace:  LIKE '%xyz%'
           with:  REGEXP 'xyz'

c) Using a sequence of LOCATE or INSTR functions.
   (should be quite fast, but I'm just guessing: so test it yourself!)

    SELECT [...]
    WHERE LOCATE('mike', productName)>0
      AND LOCATE('oldfield', productName)>0
    [...]
   or
    SELECT [...]
    WHERE INSTR(productName,'mike')>0
      AND INSTR(productName,'oldfield')>0
    [...]


[Another approach]

If searches of this kind happen very frequently, you might consider to
change your searching method completely: you could introduce a new
look-up table, which for each word that is part of any existing
productName stores the corresponding product id:
  CREATE TABLE lookup (
    word CHAR(20) NOT NULL,
    lang CHAR(2)  NOT NULL,
    pid  INT      NOT NULL,
    INDEX(word, lang)
  );
Because of the index, lookups would be very fast.  (That's the whole
point about making such a table ...)

For example, finding the products with 'mike' in the name, now is:
  SELECT pid FROM lookup WHERE word='mike' AND lang='en';
Very fast!  (No slow string-search involved anymore, only fast indexes.)

How to find pids that match all of several words?

Best put all of the search words into a temporary table,
  CREATE TABLE tmp ( word CHAR(20) );
and then:

  SELECT pid
  FROM lookup, tmp
  WHERE lookup.word=tmp.word
    AND lookup.lang='en'
  GROUP BY pid
  HAVING COUNT(*)=4;

  Instead of '4', insert the number of search words!  This way all of
  them must have been found.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Problem with Mysql and PerlDeniz Demir16 Aug
  • Re: Problem with Mysql and PerlMartin Ramsch16 Aug
  • Re: Problem with Mysql and PerlChirag Kantharia17 Aug