Re^4: in, ands, ors in query (qq)

Thanks for your help, I am still very confused and appreciate all your advice and help.

This is the entire mysql statement that runs without error but pulls records back that it shouldn't.

    my $data = qq(SELECT engine, name1, name2, name3, name4, lable1, l
+able2, lable3, lable4, settings1, settings2,
                  settings3, settings4 FROM special_fields WHERE engin
+e IN (".join(",",map($dbh->quote($_), @choices)).") AND 
                  settings1="public" OR settings2="public" OR settings
+3="public" OR settings4="public");
    my $sth = $dbh->prepare($data);
    $sth->execute() or die $dbh->errstr;

    my ($engine, $name1, $name2, $name3, $name4, $lable1, $lable2, $la
+ble3, $lable4, $settings1, $settings2,
                  $settings3, $settings4);
    $sth->bind_columns(\$engine, \$name1, \$name2, \$name3, \$name4, \
+$lable1, \$lable2, \$lable3, \$lable4, 
                       \$settings1, \$settings2, \$settings3, \$settin
+gs4);
[download]

It's long code, I apologize, there's like 12 columns in this table which makes it look messy.

The thing really confusing me is the entire JOIN statement, it was code from a previous node on here.

All I'm trying to do is have a variable (@choices) with a few names (engines column) and pull back only the records found in the array. That's why the join() is there and that's why I'm completely lost.

When I wrap ( ) around AND ( ... or ... or ... ) I get 0 results back.

Comment on Re^4: in, ands, ors in query (qq) Download Code

Replies are listed 'Best First'.
Re^5: in, ands, ors in query (qq) by tye (Sage) on Dec 13, 2006 at 03:29 UTC
Print out your statement. It isn't what you expect. Then reread what I wrote to see why. - tye	[reply]
Re^6: in, ands, ors in query (qq) by Anonymous Monk on Dec 13, 2006 at 04:21 UTC
Sorry, it's me again. I hope I'm not bugging you with the questions or get the idea I don't want to figure this out on my own but I am really stuck on this one. I took your suggestion and printed the statement and recieved `C:\Documents and Settings\Administrator\Desktop\scripts>perl form.pl SELECT engine, lable1, lable2, lable3, lable4, settings1, settings2, s +ettings3, settings4 FROM special_fields WHERE engine IN (".join(",",m +ap(->quote (), )).") AND (settings1="public" OR settings2="public" OR + settings3="public") C:\Documents and Settings\Administrator\Desktop\scripts>` [download] I've reread it quite a few times now and I don't see anything misplaced or missing. Of course that's assuming you meant for me to do `print qq(SELECT engine, lable4, settings1, settings2 FROM special_fiel +ds WHERE engine IN (".join(",",map($dbh->quote($_), @choices)).") AND + (settings1="public" OR settings2="public" OR settings3="public")); # removed some of the columns just to clean it up a bit` [download] I carefully counted the open and close parens around the JOIN and all of them were there, including the last paren after public"). I also went ahead and tried again after creating the array and something looks weird about it printing out every element at once, I sort of expected it to only print one. `C:\Documents and Settings\Administrator\Desktop\scripts>perl form.pl SELECT engine, lable1,settings3, settings4 FROM special_fields WHERE e +ngine IN (".join(",",map(->quote (), red blue orange green)).") AND (settings1="public" OR settings2="public" OR settings3="public" ) C:\Documents and Settings\Administrator\Desktop\scripts>` [download]	[reply] [d/l] [select]
Re^7: in, ands, ors in query (qq) by tye (Sage) on Dec 13, 2006 at 04:57 UTC
What you printed is what the DB is seeing. The DB doesn't know Perl so this query will return records where special_fields is either the string `".join("` or is the string `",map(->quote(), red blue orange green))."`. You aren't getting any records returned because you don't have any records with either of those two strings in that column (not surprisingly). You need to have that Perl code executed, so you have to take that code outside of the quotes, that is, outside of the `qq( ... )`. The quote (") in this code: `engine IN (".join(` [download] you appear to think is ending the quoted string such that Perl executes the concatenation operator (.) and calls the join function. But, as I said way up there, a quote (") doesn't end a quoted string that was started with `qq(`. I'm going to stop right there because I think you can figure out the rest. You appear to just have had a hard time seeing this. No, I'll give you one more hint. You might want to change from `qq( ... )` to something like `qq{ ... }`, `qq< ... >`, or `qq[ ... ]`, because ending a `qq( ... )` string having unmatched parens (which is what the first part of your statement needs to be) is a bit tricky. Though you might want to experiment with the trickier syntax after you get it working just for your own enlightenment. Good luck. - tye	[reply] [d/l] [select]