Programming the Perl DBI

Slice array reference argument

It is also possible to use fetchall_arrayref( ) to return a data structure containing only certain columns from each row returned in the result set. For example, we might issue an SQL statement selecting the name, site_type, location, and mapref fields, but only wish to build an in-memory data structure for the rows name and location.

This cannot be done by the standard no-argument version of fetchall_arrayref( ), but is easily achieved by specifying an array slice as an argument to fetchall_arrayref( ).

Therefore, if our original SQL statement was:

SELECT meg.name, st.site_type, meg.location, meg.mapref
FROM megaliths meg, site_types st
WHERE meg.site_type_id = st.id

then the array indices for each returned row would map as follows:

name       -> 0
site_type  -> 1
location   -> 2
mapref     -> 3

By knowing these array indices for the columns, we can simply write:

### Retrieve the name and location fields...
$array_ref = $sth->fetchall_arrayref( [ 0, 2 ] );

The array indices are specified in the form standard to Perl itself, so you can quite easily use ranges and negative indices for special cases. For example:

### Retrieve the second last and last columns
$array_ref = $sth->fetchall_arrayref( [ -2, -1 ] );

### Fetch the first to third columns
$array_ref = $sth->fetchall_arrayref( [ 0 .. 2 ] );

The actual data structure created when fetchall_arrayref( ) is used like this is identical in form to the structure created by fetchall_arrayref( ) when invoked with no arguments. ...