select 
    foo
from
    bar
where
    baz in (1,2,3)
    and
    (
        quux = 4
        or
        bar2 = 5  -- running out of funny names
    )
[download]

Hi.

I did try to put parenthesis around the AND but now nothing is pulled back from the database. I know without a doubt that @choices containes the engine's names properly but with the parenthesis, nothing mathches. Without it, everything that has settings1-4 to "public" load.

    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");
[download]

Your Perl syntax is broken. If you start a quoted string with qq(, then it won't end until the matching ), but you have a double quote followed by Perl code that you clearly expect to run (".join...). So please cut'n'paste the actual code you are using (since you say you get rows returned you must have a working query, just not quite the right query).

- tye        

As tye suggested, you need parentheses around all of the OR options too.

Same as in a Perl expression: ... AND ( ... OR ... OR ... )

my @getfields = qw/engine/;
for my $type ( qw/name lable settings/ ) {
    for my $num ( 1 .. 4 ) {
        push @getfields, "$type$num";
    }
}
my $sql = 'select '.
          join( ',', @getfields ).
          ' from special_fields WHERE engine IN ('.
          join( ',', ('?') x scalar @choices ).
          ') AND ('.
          join( ' OR ', map { "settings$_ = 'public'" } 1..4 ).
          ')';

my $sth = $dbh->prepare( $sql );
$sth->execute( @choices );
[download]

• "$data" is not a good name for a string that is supposed to store an SQL "select ..." statement;
• having one array to store the names of all the columns being selected can be really handy later;
• maybe the for loop for putting the names into an array is overkill, but it does save you from having to type the same names four times each;
• you really should be using placeholders for variable values that are being plugged into the query, and passing those values when you execute the query;
• formatting the lines of code for legibility in a narrower window can really help.

I personally find that style of sql construction hard to read. How about this:

my $sql = do {
  my $getfields = join ',', @getfields;
  my $placeholders = join ',', ('?') x @choices;
  my $settingspublic = join ' OR ', map "settings$_ = 'public'", 1 .. 
+4;

  <<EOF;
  select $getfields
    from special_fields
    where engine in ($placeholders)
      and ($settingspublic)
EOF
};
[download]

We're building the house of the future together.

WHERE engine IN (...) AND 'public' IN (settings1, settings2, settings3
+, settings4)
[download]

my $engines = join(",",map($dbh->quote($_), @choices));
my $sql = <<EOF;
  SELECT engine, name1, name2, name3, name4, lable1, lable2, lable3, l
+able4, settings1, settings2,
                  settings3, settings4
  FROM special_fields
  WHERE engine IN ( $engines )
    AND (
       settings1="public" OR settings2="public" OR settings3="public" 
+OR settings4="public"
  )
EOF
[download]

my $qs = join(",",map('?', @choices));
my $sql = <<EOF;
  SELECT engine, name1, name2, name3, name4, lable1, lable2, lable3, l
+able4, settings1, settings2,
                  settings3, settings4
  FROM special_fields
  WHERE engine IN ( $qs )
    AND (
       settings1="public" OR settings2="public" OR settings3="public" 
+OR settings4="public"
  )
EOF
my $rows = $dbh->selectall_arrayref( $sql, {Slice=>{}}, @choices );
[download]

use SQL::Abstract;
my $sa = SQL::Abstract->new( logic=>'and' );
my($sql, @bind) = $sa->select(
    'special_fields',
    [qw/
        engine name1 name2 name3 name4
        lable1 lable2 lable3 lable4
        settings1 settings2 settings3 settings4
    /],
    [
      { engine => \@choices, },
      [
        settings1 => 'public',
        settings2 => 'public',
        settings3 => 'public',
        settings4 => 'public',
      ]
    ],
);
my $rows = $dbh->selectall_arrayref( $sql, {Slice=>{}}, @bind );
[download]