List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:August 16 1999 6:17pm
Subject:Re: Problem with Mysql and Perl
View as plain text  
At 09:49, 19990816, Deniz Demir wrote:
>I have a problem...
>
>@keyN = split(' ', $value);
>
>$statement = "select * FROM cds WHERE productName LIKE '%@keyN%' AND language LIKE
> '%$keywordsLanguage%'";

You need something like this:

    @keyN = map { s/'/''/g } split ' ', $value;  # escape single quotes

    if (@keyN) {
        $statement =
            "SELECT * FROM cds WHERE " .
            join(' AND ', map { "productName LIKE '%" . $_ . "%'" } @keyN) .
            " AND lanugage LIKE '%$keywordsLanguage%'";
        
        $dbh->prepare($statement);

        # and so forth
    } else {
        print "No key words were requested\n";
    }

If you want to match only whole words, you might want to use REGEXP
instead of LIKE, and match on word boundaries.  Or you might want to
do something altogether different.

>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.

"Associative array" is a very specific term, and it doesn't apply
here.  You're using a normal "array", or "list".  An "associative
array" is a map from key to value.  In Perl, they look like this:

    # this is an associative array, also called a HASH value
    %assoc;

    $assoc{'key'} = $value;


>Method I used lists records which contains any element of
>associative array, but it must contain all element of
>associative array. How can I make it do this? Any other operator
>except LIKE or character % or usage...

If you are doing a lot of this, and you find that the speed of your
queries is not good enough, you might want to consider using some
separate tables to hold indexes to all of your product names.  If
you want to do this, there are some tools already built that will
help you with it.  Look at the mailing list archive, or the MySQL
web site, for more info.

>Also at first I wrote:
>
>@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)

You could have used this:

    @keyN  = split(/\+/, $value);

Please read the perlfunc(1) man page for the details.

>and I have replaced this statement with above (split with respect to space)....

Tim
Thread
Problem with Mysql and PerlDeniz Demir16 Aug
  • Re: Problem with Mysql and PerlThimble Smith16 Aug