First, please read How (Not) To Ask A Question, in particular the section on titles.

This is not a Perl issue, but an SQL issue. You can resolve this by using the IN operator.

As a side note, you should seriously consider using placeholders in your code. It will handle any character escaping that has to be performed and adds a layer of security. As an example, here is how you might form your $sql variable for finding all occurrences in your year array:

my $sql = "SELECT DISTINCT 
                               
                               name, email, addr, location
                               
               FROM            my_users
               WHERE           year IN (" . join(', ', ('?') x @year) 
+. ")
          ";
$i = 0;
my $query = $dbh->prepare($dbh) or die "Prepare failure: ".$dbh->errst
+r;
foreach my $year (@year) {
    $query->bind_param(++$i,$year);
}
$query->execute or die "Execute failure: ".$dbh->errstr;
[download]

For IN clauses (especially that many IN clauses), if there are variable numbers of parameters, and especially if the statement (with the fixed number of parameters) is not reused, I wouldn't bother with placeholders. The DBI quote() method is fine.

And I disagree about the !Perl but SQL issue. It was a Perl/SQL issue IMHO.

Yeah, my language is a little strong. I agree that quote is fine for a single use query, but I think that in the best case scenarios they tie and in worst cases placeholders fare dramatically better, so they should be in a programmer's arsenal. I also think that performing the N^M queries necessary to do this as a series of one-offs is unnecessary abuse of a database when 1 straight forward query will do.

In any case, TIMTOWTDI and best practice can mean ignoring Best Practice.

On an unrelated note, "$!" does not hold any useful information for a failed DBI connect(). I recommend using the RaiseError attribute in the connect call (see the DBI docs).

Could this be correct or is there a more efficient way of doing it?



#...more code above, all variable previously declared and strict is al
+so been used.

push @all_names, $names;
push @email, $email;
push @local, $local;
push @year, $year;

} #end of foreach.


#Getting data

my $dbh = DBI->connect("DBI:ODBC:$myserver",$u, $p, { RaiseError => 1 
+});

my $vals = 
  join("," 
       ,map { $dbh->quote($_) } @all_names 
      );

my $year_val = 
  join("," 
       ,map { $dbh->quote($_) } @year 
      );

my $email_val = 
  join("," 
       ,map { $dbh->quote($_) } @email 
      );

my $local_val = 
  join("," 
       ,map { $dbh->quote($_) } @local 
      );

my $sql = "SELECT DISTINCT 
                               
                               name, email, addr, location
                               
               FROM            my_users
               WHERE           year IN ($year_val)   /* need all the v
+alues from @year */
               AND             email IN ($email_val) /* need all the v
+alues from @email */
               AND             location IN ($local) /* need all the va
+lues from @local */
               AND             name IN ($vals)
          ";
[download]

use strict;
use SQL::Abstract;

my @all_names = qw/one two three/;
my @email     = qw/first_email second_email third_email/;
my @local     = qw/here there and_everywhere/;
my @year      = qw/1970 1980 1990/;
my @order     = qw/location name/;

my $sql = SQL::Abstract->new;

my @fields = qw/name email addr location/;
my $table  = 'my_users';

my %where = (
    year     => \@year,
    email    => \@email,
    location => \@local,
    name     => \@all_names,
);

my ( $stmt, @bind ) = $sql->select( $table, \@fields, \%where, \@order
+ );

print "$stmt\n";
print join '|', @bind;
[download]

SELECT name, email, addr, location
FROM my_users
WHERE ( ( ( email = ? OR email = ? OR email = ? ) AND
( location = ? OR location = ? OR location = ? ) AND
( name = ? OR name = ? OR name = ? ) AND
( year = ? OR year = ? OR year = ? ) ) )
ORDER BY location, name

first_email|second_email|third_email|
here|there|and_everywhere|
one|two|three|
1970|1980|1990
[download]

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