swandown has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

please forgive if this is a very newbie question. I have course data returned from DB by something like: 

$sql = qq{select course_id,course_name,course_sitting_id,course_sittin
+g_date from courses};
$sth = $dbh->prepare($sql) or die . . .;
$sth->execute() or die  . . . .;
while (@fetch = $sth->fetchrow)
        { . . . .

[download]

Which returns data like : 

10,Programming 101,1,01-SEP-2011
20,Databases 101,1,03-SEP-2011
10,Programming 101,2,20-SEP-2011
20,Databases 101,2,27-SEP-2011

[download]

I need to get it into the following hash of hashes structure :

my %courses= (
    '10' => {
        'desc' => 'Programming 101',
        'sittings' => {
            '1' => '01-SEP-2011',
            '2' => '20-SEP-2011',
        },
    },
    '20' => {
        'desc' => 'Databases 101',
        'sittings' => {
            '1' => '03-SEP-2011',
            '2' => '27-SEP-2011',
        },
    },
);

[download]

But I'm completely stumped . . wish I could show what code I got . . . but I got nothing . . . . Many Thanks.

Replies are listed 'Best First'.
Re: Hash of Hashes
by toolic (Bishop) on Aug 30, 2011 at 12:27 UTC 
    use warnings;
use strict;
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;

my %courses;
while (<DATA>) {
    chomp;
    my ($id, $desc, $sit, $date)    = split /,/;
    $courses{$id}{desc}             = $desc;
    $courses{$id}{sittings}{$sit}   = $date;
}

print Dumper(\%courses);

__DATA__
10,Programming 101,1,01-SEP-2011
20,Databases 101,1,03-SEP-2011
10,Programming 101,2,20-SEP-2011
20,Databases 101,2,27-SEP-2011

    [download]
    prints... 
    $VAR1 = {
          '10' => {
                    'desc' => 'Programming 101',
                    'sittings' => {
                                    '1' => '01-SEP-2011',
                                    '2' => '20-SEP-2011'
                                  }
                  },
          '20' => {
                    'desc' => 'Databases 101',
                    'sittings' => {
                                    '1' => '03-SEP-2011',
                                    '2' => '27-SEP-2011'
                                  }
                  }
        };

    [download]
[reply]
[d/l]
[select]

      Superb! Thank you so much toolic and Corion.

[reply]
Re: Hash of Hashes
by Corion (Patriarch) on Aug 30, 2011 at 12:22 UTC

    The first thing is to change your query:

    select
    course_id,course_name,course_sitting_id,course_sitting_date
from courses
order by course_id, course_name, course_sitting_id

    [download]

    Then, you just need to realize that whenever the course_name changes, you want to begin a new entry in your results.

    ...
my %courses;
for my $row (@results) {
    my $course_id = $row->[0];
    $courses{ $course_id } ||= {
        ...
        desc => $row->[1],
        sittings => {},
        ...
    };
    $courses{ $course_id }->{ sittings }->{ $row->[2] } = $row->[3];
};

    [download]

    Also see perldsc and Data::Dumper.

[reply]
[d/l]
[select]
Re: Hash of Hashes
by Neighbour (Friar) on Aug 30, 2011 at 14:18 UTC
    Even though your question is already answered, here's a different approach.
    my $hr_data = $dbh->selectall_hashref(
    "select
        course_id,
        course_name,
        course_sitting_id,
        course_sitting_date 
    from courses", 
    ["course_id", "course_sitting_id"]) or die $dbh->errstr;

    [download]
    This gets you the following structure: 
    {
    '10' => {
        '1' => {
            course_id => '10',
            course_name => 'Programming 101',
            course_sitting_id => '1',
            course_sitting_date => '01-SEP-2011'
        },
        '2' => {
            course_id => '10',
            course_name => 'Programming 101',
            course_sitting_id => '2',
            course_sitting_date => '20-SEP-2011'
        },
    },
    '20' => {
        '1' => {
            course_id => '20',
            course_name => 'Databases 101',
            course_sitting_id => '1',
            course_sitting_date => '03-SEP-2011'
        },
        '2' => {
            course_id => '20',
            course_name => 'Databases 101',
            course_sitting_id => '2',
            course_sitting_date => '27-SEP-2011'
        },
     },
};

    [download]
    From here you can mold the hashref to your preference.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom