The first is to do:

my $ids_in = join ", ", @in;
my $sql = "select id, size from table where id in ($ids_in)";
[download]

UPDATE
Forgot some silly parens. Thanks runrig.

In my projects, I had routines like the following :

sub selectAll {
  my ($what,$table,@clauses);
  my $where = "";
  if (@clauses) {
    $where = " WHERE " . join(" and ",@clauses);
  };
  my $statement = "SELECT $what FROM $table $where";
  my $sth = $dbh->prepare($statement);
  $sth->execute() or die "SQL: selectAll: '$statement' failed.";
  return $sth->fetchall_arrayref();
};
[download]

Nowadays, I'm using mostly prepared SQL queries with prepared parameters, as this gives automatic quoting, parameter count checking and syntax checking before that statement is executed the first time. If you don't have dynamic tables (and if you have, you should maybe rethink your database layout), the following should work for you :

my $sthGetFile = $dbh->prepare("select VISUAL,LINK from FILES where (I
+D=?)");

sub getFile {
  my ($file) = @_;
  $sthGetFile->execute($file);
  return $sthGetFile->fetchrow_arrayref();
};

# Assuming $id exists in the table
print getFile($id)->[1];
[download]

Update: On rereading the original post, I think the real answer is

my @id = (1,2,3,4,6,7,8);
my $clause = "";
if (@id) {
  $clause = "where ID in (" . join(",",@id) . ")";
};

my $statement = "select ID,COLOR from TABLE $clause";
...
[download]

my @sth;

while (<DATA>) {
  my @id = split /,/, $_;

  $sth[@id] ||=
    $dbh->prepare(
      'SELECT id, size FROM table WHERE id IN (' .
      join(',', ('?') x @id) . ')'
    );
  $sth[@id]->execute(@id);
  while (my($id, $size) = $sth[@id]->fetchrow_array()) {
    print "$id $size\n";
  }
}
__DATA__
1,4,6
7,10
9
2,5,8
3
[download]

The statement handle is executed on the next line, with the values in @id bound to the placeholders. The results are fetched and processed, and then the process starts over with new values in @id. Note that @id is in a scalar context in $sth[@id] and ('?') x @id, and a list context in execute(@id).

mysql supports the IN() clause. So it might be easiest to use this:

Which i would build this way, but there are plenty of others:

my $idlist = join(',', map($dbh->quote($_),@id));
$statement = "SELECT id, size FROM table WHERE id IN ($idlist)";
[download]

Apparently there are issues with the length of @id: past a certain point it would need to be divided into chunks for this approach to work.

update *sigh*. redundant again.

use DBI;
my $dbh = DBI->connect("DBI:mysql:database=your_db");
my $where = "(id = '" . join ("' OR id = '", @ids) . "')";
my $sth = $dbh->prepare("SELECT * FROM event WHERE $where");
[download]

while ($ref = $sth->fetchrow_hashref) {
    &do_stuff_with($ref->{'db_col_u_like'});
}
[download]

bout the problem of an empty array i already thought, but the array shouldnt be empty it is filled with values out of a form, which invokes the script... but u never know... would it be some solution to put a zero as first id in the array, cause the zero wont be an id in the db table

Yes, or just put the whole thing in an if (@ids) { ... }loop - which would make it work quicker when @ids was empty

§ George Sherston

$statement = "SELECT id, size FROM table WHERE ";
for ($x = 0; $x < @id; $x++) {
  if ($x > 0) {
   $statement .= " || "; 
  }
  $statement .= "id = '$id[$x]'";
}
[download]

$where_clause = 'where id in ('.join(",",("?") x @id).')';
$sql .= $where_clause;
my $sth = $dbh->prepare($sql);
$sth->execute(@id);
[download]

$sql .= 'where id = ?';
my $sth = $dbh->prepare($sql);
for my $id (@id) {
 $sth->execute($id);
...
}
[download]

Update: I like tilly's temp table idea. I've actually done that in some cases.