Who is this for
This article is for programmers who want to learn how to use multi-dimensional arrays with the DBI module.

Databases referred in this article are relational databases

Basic Perl programming, basic DBI module methods and multi-dimensional array concepts

Introduction
The DBI module is a relational database interface that shields a programmer from the details of accessing a specific database. Without the DBI interface, there is no standard way of accessing a database from Perl. In a worst case scenario, the number of ways of say, connecting to a database will depend on the number of databases out there in the market. The DBI module provides a standard so Perl programmers can use different databases in the same way.

Different ways of accessing a database
Databases were created to store data. This requires that a program be able to read, add, change or delete records on the database. For relational databases, these are done using SQL statements.

Multi-dimensional arrays are used in reading database records. The DBI module uses multi-dimensional arrays in the selectall_arrayref, fetchall_arrayref methods.

There are other methods that return array references but the arrays referred to by these other functions are just one-dimensional arrays. These two methods return multi-dimensional arrays (2-dimensional arrays to be exact).

Sample code
The example here assumes that we are using the mySQL database.

Assume this code:

# Connect to the database

my $db = DBI->connect(
	'DBI:mysql:test:host=localhost', 
	'userid',
	'password');

my $sql = 'SELECT SITENDX, 
	SITEDESC, 
	SITELOGON FROM SITE';

my $sth =
$db->prepare($sql);

$sth->execute();

my $ref = $sth->fetchall_arrayref;

This first statement connects to the daatbase.
The next sets the SQL statement used in the $db->prepare($sql) statement following it. This returns a statement handle ($sth).
The statement is executed using the statement handle ($sth).
All the records are returned as a multi-dimensional array reference ($ref).

Or a simpler one with the same results:

# Connect to the database

my $db = DBI->connect(
	'DBI:mysql:test:host=localhost', 
	'userid',
	'password');

my $sql = 'SELECT SITENDX, 
	SITEDESC, 
	SITELOGON FROM SITE';

my $ref = $db->selectall_arrayref($sql);

The code connects to the database and selects all the records based on the SQL statement. The result is an array reference ($ref). The array referred to is a two dimensional array. The first dimension contains a reference to the rows from the SQL statement.

So if you say $ref->[0] or $ref->[1], it refers to another array reference that contains the first and second records respectively.

The second dimension contains the respective fields for the row.

So if we say $ref->[0][0], we will get the SITENDX of the first record. $rec->[4][3] will get the SITELOGON of the fourth record and so on.

More information

For more information on the DBI module, please check out this article.