#### #!/bin/perl5 use strict; use warnings; use Data::Dumper; use DBI; use HTML::Template; $|++; my $dbh = DBI->connect( "DBI:mysql:database=map;host=localhost", "", "", {RaiseError => 1} ); my $t = HTML::Template->new( filename => 'subjects_tmpl.html', ); # my $category_loop = get_category_loop(); # die Dumper $category_loop; $t->param( category_loop => get_category_loop() ); open my $fh, '>', 'subjects.html' or die "can't open to write: $!\n"; print $fh $t->output; close $fh; sub get_category_loop{ my $sql_category = q{ SELECT category_id, category_name FROM category ORDER BY category_id }; my $sql_subject = q{ SELECT subject_id, subject_name FROM subject WHERE category_id = ? ORDER BY subject_name }; my $sth_subject = $dbh->prepare($sql_subject); return [ map{add_subject_loop($sth_subject, $_)} @{$dbh->selectall_arrayref($sql_category, {Slice => {}})} ]; } sub add_subject_loop{ my ($sth, $category_loop) = @_; $sth->execute($category_loop->{category_id}); $category_loop->{subject_loop} = $sth->fetchall_arrayref({}); delete $category_loop->{category_id}; # not used by the tmpl return $category_loop; } #### $dbh->selectall_arrayref($sql_category, {Slice => {}}) #### $sth->fetchall_arrayref({}) #### return [ map{add_subject_loop($sth_subject, $_)} @{$dbh->selectall_arrayref($sql_category, {Slice => {}})} ]; #### $VAR1 = [ { 'subject_loop' => [ { 'subject_id' => '2', 'subject_name' => 'africa' }, { 'subject_id' => '101', 'subject_name' => 'congo' }, 'category_name' => 'africa' }, { 'subject_loop' => [ { 'subject_id' => '10', 'subject_name' => 'argentina' }, { 'subject_id' => '11', 'subject_name' => 'bolivia' }, { 'subject_id' => '12', 'subject_name' => 'brazil' }, 'category_name' => 'americas' }, ####

subjects