Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

SQLite select or

by Anonymous Monk
on Nov 18, 2013 at 15:45 UTC ( #1063130=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear monks

What is wrong with the following?

my $select_1="lan1"; my $select_2="lan2"; my $all_select = $dbh->selectall_arrayref("SELECT ID,lan1,lan2,lan3,la +n FROM table WHERE LOWER($select_1) LIKE '%$Word%' OR LOWER($select_ +1) LIKE '%$Word'");

what I want is to finde entries which contain $Word in column lan1 OR in column lan2. It now finds only $Word in column lang1.

Replies are listed 'Best First'.
Re: SQLite select or
by erix (Parson) on Nov 18, 2013 at 17:50 UTC
     OR LOWER($select_1) LIKE '%$Word'"

    Looks like you have forgotten the final % at the end of the second LIKE expression.

    It should be

     OR LOWER($select_1) LIKE '%$Word%'"

      thank you...this was the problem. Shame on me! 2 Hours try to understand what was wrong.

        Q:   Why do programmers have flat heads?
        A:   (whack!) “D‘oh!!!”

        This sort of thing happens to everyone.   More frequently than we usually care to admit.

Re: SQLite select or
by Corion (Pope) on Nov 18, 2013 at 15:52 UTC

    To help us help you better, please tell us the following information:

    What does your resulting SQL string look like?

    Does the resulting SQL string work when you paste it into the command line client?

Re: SQLite select or
by toolic (Bishop) on Nov 18, 2013 at 15:52 UTC
    Maybe because you never use $select_2 ?

      I'm sorry! It should be:

      my $all_select = $dbh->selectall_arrayref("SELECT ID,lan1,lan2,lan3,la +n4 FROM table WHERE LOWER($select_1) LIKE '%$Word%' OR LOWER($select +_ +2) LIKE '%$Word'");

      I just made an error when coping it (and simplifying it a bit). Using:

      foreach my $row_db (@$all_select) { my ($ID, $lang1, $lan2, $lan3, $lan4) = @$row_db; print "$lang1\t$lang2) }

      This prints out rows only if $Word is in column lan1

        Please paste the real code into the post, not some typed-in impression of what the code might look like.

        The code as it is above has several syntax errors that mean that the code you show cannot be the code you run.

        Again, compare the SQL you construct with the SQL that you manually run in the command line client.

Re: SQLite select or
by NetWallah (Canon) on Nov 18, 2013 at 16:36 UTC
    You need to escape the percent signs, which are intended for SQL, such that they are not intrapolated by perl.

                 When in doubt, mumble; when in trouble, delegate; when in charge, ponder. -- James H. Boren

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1063130]
Approved by toolic
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (2)
As of 2018-07-22 16:46 GMT
Find Nodes?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?

    Results (454 votes). Check out past polls.