Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

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 (Abbot) 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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1063130]
Approved by toolic
help
Chatterbox?
[Corion]: marto: We could vote, unconsider and reconsider ;)
[Corion]: "vote keep", I meant ;)
[Corion]: marto: But that aside, I hope you are well!
Corion is slowly acclimatizing to working only four days a week
[marto]: the back pain is still there, it's been a rough week, trying to do building work with a bad back
[Corion]: marto: Ow! That doesn't sound good :-/ The work won't help your back...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (12)
As of 2017-09-21 14:42 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    During the recent solar eclipse, I:









    Results (248 votes). Check out past polls.

    Notices?