We have to print out the entries of a database in year-wise, month-wise and day-wise hierarchy.
The best and easiest( that is with a bit of perl advanced data structures understanding) way I have found with a little of investigation, is by using Perl's Advanced Hash and Array data structures.
The Perl's sort command/inbuilt sorting algorithm was also used to easily sort arrays and Hash keys.
The task in hand is, I have date and amount columns in the data base with values as below:
2012-06-01 22:47:04 999
2012-05-02 22:47:04 228
2012-06-09 22:47:04 222
2014-05-01 22:47:04 333
2014-08-01 22:47:04 444
2015-05-01 22:47:04 555
2015-04-03 22:47:04 111
Then I need to lay them out in this order
2012 - Year
05 - Month
02 - Day
228
06 - Month
01 - Day
999
09 - Day
222
2014 - Year
05 - Month
01 - Day
333
08 - Month
01 - Day
444
2015 - Year
04 - Month
03 - Day
111
05 - Month
01 - Day
555
First I access these rows from the database and segregate out the values in to Hash of Hash of Hash of Array with the below while-loop:
while (my $h = $sth->fetchrow_hashref())
{
($year, $month, $day) = split(/\-/, $h->{pgdate});
$day =~ s/([0-9]) .*/$1/;
push @{ $dateHsh{$year}{$month}{$day} }, $h->{pgid};
}
Now I have created the data structure %dateHsh.
Finally in order to print the formatted layout where I have chosen to print it out in a nested HTML Definition List with the below foreach loop:
foreach my $syear ( sort keys %dateHsh) {
print "<dl>";
print "<dt>$syear - Year </dt><dd></dd>";
for my $smonth ( sort keys %{ $dateHsh{$syear} } ) {
print "<dl><dt>$smonth - Month </dt><dd></dd>";
for my $sday ( sort keys %{ $dateHsh{$syear}{$month} } ) {
print "<dl><dt>$sday - Day </dt><dd></dd></dl>";
for my $val ( my @sorted = sort { $a <=> $b } @{ $dateHsh{$syear}{$smonth}
{$sday} } ) {
print "<dl><dt>$val</dt><dd></dd></dl>";
}
}
}
print "</dl></dl>";
}
Summary:
With a little bit of programming knowledge we can quickly retrieve/pull data from databases put them into a orderly in-memory data structure and then rearrage the in-memory datastructure(s) for printing it out to what ever format we require.
Tags: perl sort data structure
