mysql> select * from employee;
 +----+---------+--------+------------+
 | id | name    | salary | hiredate   |
 +----+---------+--------+------------+
 |  1 | Fran    | 100000 | 2006-05-01 |
 |  2 | Barry   |  90000 | 2006-04-01 |
 |  3 | Anna    |  80000 | 2006-01-01 |
 |  4 | Cynthia |  70000 | 2006-02-01 |
 |  5 | Enrico  |  60000 | 2006-03-01 |
 |  6 | Derek   |  50000 | 2006-06-01 |
 +----+---------+--------+------------+
 6 rows in set (0.00 sec)

##</code><code>##

#!/usr/bin/perl -wT
#
#  Written to illustrate various mechanisms of data retrieval from
#  a mysql database 'new'.
#
#  060402 liverpole -- created.
#

##############
### Strict ###
##############
use strict;
use warnings;


#################
### Libraries ###
#################
use Data::Dumper;
use DBI;


####################
### Main program ###
####################
my $dbh = connect_to_dbase('DBI:mysql:database=new', 'root', 'rootpass');
my $sql = "SELECT * FROM employee";

my $p1 = sql_hash($dbh, $sql);
printf "Results as hash  => '%s'\n", Dumper($p1);

my $p2 = sql_array($dbh, $sql);
printf "Results as array => '%s'\n", Dumper($p2);



###################
### Subroutines ###
###################
sub connect_to_dbase {
    my ($dsn, $user, $pass) = @_;
    my $dbh = DBI->connect($dsn, $user, $pass, { 'AutoCommit' => 1 });
    defined $dbh or die "Cannot connect to database '$dsn'\n";
    return $dbh;
}


sub sql_hash {
    my ($dbh,$sql) = @_;
    my $sth = $dbh->prepare($sql);
    $sth->execute() or die "Failed to execute SQL '$sql'\n";
    my @data;
    while (my $p = $sth->fetchrow_hashref) {
    	push @data, $p;
    }
    return \@data;
}


sub sql_array {
    my ($dbh,$sql) = @_;
    my $sth = $dbh->prepare($sql);
    $sth->execute() or die "Failed to execute SQL '$sql'\n";
    my @data;
    while (my $p = $sth->fetchrow_arrayref) {
        push @data, [ @$p ];
    }
    return \@data;
}

##</code><code>##

Results as hash  => '$VAR1 = [
          {
            'name' => 'Fran',
            'hiredate' => '2006-05-01',
            'id' => '1',
            'salary' => '100000'
          },
          {
            'name' => 'Barry',
            'hiredate' => '2006-04-01',
            'id' => '2',
            'salary' => '90000'
          },
          {
            'name' => 'Anna',
            'hiredate' => '2006-01-01',
            'id' => '3',
            'salary' => '80000'
          },
          {
            'name' => 'Cynthia',
            'hiredate' => '2006-02-01',
            'id' => '4',
            'salary' => '70000'
          },
          {
            'name' => 'Enrico',
            'hiredate' => '2006-03-01',
            'id' => '5',
            'salary' => '60000'
          },
          {
            'name' => 'Derek',
            'hiredate' => '2006-06-01',
            'id' => '6',
            'salary' => '50000'
          }
        ];
'
Results as array => '$VAR1 = [
          [
            '1',
            'Fran',
            '100000',
            '2006-05-01'
          ],
          [
            '2',
            'Barry',
            '90000',
            '2006-04-01'
          ],
          [
            '3',
            'Anna',
            '80000',
            '2006-01-01'
          ],
          [
            '4',
            'Cynthia',
            '70000',
            '2006-02-01'
          ],
          [
            '5',
            'Enrico',
            '60000',
            '2006-03-01'
          ],
          [
            '6',
            'Derek',
            '50000',
            '2006-06-01'
          ]
        ];
'

##</code><code>##

sub sql_hash {
    my ($dbh,$sql) = @_;
    my $sth = $dbh->prepare($sql);
    $sth->execute() or die "Failed to execute SQL '$sql'\n";
    my @data;
    while (my $p = $sth->fetchrow_hashref) {
        push @data, $p;
    }
    my @sorted = sort { $a->{'name'} cmp $b->{'name'} } @data;
    return \@sorted;
}