In your question it is not clear, which order you want, the order of the hash keys retrieved from the database, or the order of records from the database. As it is, that doesn't matter, because neither has an order.
If you want an order on the hash keys, you have to define it yourself when outputting your data.
If you want an order in the records as they are returned from the database, you have to supply the appropriate ORDER BY clause to your SQL.
If you show us the relevant code, and the output you get, maybe we can give you more exact help.
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)The following program:
#!/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;
}
illustrates the differences when you fetch the data using a hashref or an arrayref:
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'
]
];
'
But if you want the order stored in a particular way, for example, sorted by employee name, you will either have to perform the ordering in the SQL statement (eg. "SELECT * FROM employee ORDER BY name"), or you will have to perform the sort after retrieving the data from the database. For example, to sort the hash references by name:
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;
}
SELECT field1, field2, etc. FROM...
Alternatively, SELECT * will return the data in the order the fields are stored in the table, but again, you need to ask for the results as an array rather than a hash. fetchrow_hashref() is not something I personally find terribly useful.
perlmonks.org content © perlmonks.org and Corion, dumpexec, liverpole, TedPride
prlmnks.org © 2006 edmund von der burg (eccles & toad)
v 0.03