Re: Retrieving multiple records from database

[gmax]

Use the "IN" operator instead of "OR". Here's my favorite idiom (using placeholders):

my @ids_to_match = ( ... );
my $query = "SELECT somedata from table WHERE id IN ("
    . join(",", map {"?"} @ids_to_match )
    .")";

my $sth = $dbh->prepare($query);

$sth->execute(@ids_to_match);

Alternatively:

my $query = "SELECT somedata from table WHERE id IN ("
    . join(",", map { $dbh->quote($_) } @ids_to_match )
    .")";

my $sth = $dbh->prepare($query);

$sth->execute();

See DBI Recipes for more idioms.

[mpeppler]

This is a really neat idiom, but there is a caveat: some database engines impose a limit on the number of items in an IN clause, and/or impose a limit on the number of placeholders that can be used in a SQL statement.

Michael

[bradcathey]

Thanks gmax (I was secretly hoping you'd see this OP). Ended up with a more familier:

$stmt = qq/SELECT somedata FROM table WHERE id IN (/ . join(',', ('?')
+ x @ids_to_match ) . qq/)/;
 $sth = $dbh->prepare($stmt);
 $sth->execute(@ids_to_match);
 my $results = $sth->fetchall_arrayref({});

My next question is why does fetchrow_array() only return an array of 1 element, but fetchall_arrayref({}) nabs them all?

[jZed]

My next question is why does fetchrow_array() only return an array of 1 element, but fetchall_arrayref({}) nabs them all?

Well the difference between fetch*row* and fetch*all* explains why fetchrow gets just one row and fetchall gets them all. The difference between fetchrow_*array* and fetchrow_*arrayref* explains why using a scalar returns 1 value for an array and all values into an arrayref. If you want the full row, you need to do @row = fetchrow_array(), not $row = fetchrow_array().

[davido]

The array that "fetchrow_array()" returns is the selected columns (each in their own element of the array) from a single row. If your query grabbed multiple rows, you need to keep calling fetchrow_array() to get the next row, and so on. Undef will indicate you're done.

fetchall_arrayref() fetches all of the rows, in a multidimensional array where the 2nd dimension is the columns.

[bradcathey]

Thanks, davido, I ended up with this in my final app:

while ($row = $sth->fetchrow_array()) {
    push (@categories, $row);
}

I guess fetchrow and fetchall we appropriately named ;^) Now, I wish there were a fetchcol that just retrived a known column of a given row (intersection, so selectcol_arrayref wouldn't work) so I don't have to do:

$single_value = $categories[0];

after the fetch, and could just do:

$single_value = $sth->fetchcol_justone;