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

Fellow monasterians,

Using data from a MySQL table, I'm setting up an an AoH for a HTML::Template loop. I start by selecting all the columns because I need a couple 'non-displayable' columns for testing a condition. If the condition is met I want to assign just a few of the key/value pairs of the successful array element to the H::T loop's AoH.

Here's what I have based on something is saw in the Cookbook, pg 105, but is obviously wrong. Can something like this even work?

my $stmt = "SELECT * FROM table WHERE ...
#execute...

my $sqldata = $sth->fetchall_arrayref({});    
my @events;

for my $i ( 0 .. $#$sqldata ) {            
   if ( $sqldata->[$i]{'col5'} eq $sqldata->[$i]{'col8'} ) {
      push ( @events, $sqldata->[$i]{'col1', 'col2', 'col3'} );
   }
}    

my $template = HTML::Template->new( filename => 'some.tmpl' );
$template -> param( events => \@events );
#etc...

[download]

or am I resigned to: 

$events[$ctr]{'col1'} = $sqldata->[$i]{'col1');
$events[$ctr]{'col2'} = $sqldata->[$i]{'col2');
$events[$ctr]{'col3'} = $sqldata->[$i]{'col3');
$ctr++;

[download]
Thanks all!

—Brad
"A little yeast leavens the whole dough."

Replies are listed 'Best First'.
Re: Pushing a 'hash slice' to an AoH
by borisz (Canon) on Jun 11, 2004 at 16:45 UTC
    Another way is to use the database a bit more: 
      my $data = $dbh->selectall_arrayref(q{
    SELECT 
      col1, col2, col3 
    FROM 
      your_table
    WHERE 
      col5 = col8
  }, { Slice => {} });

my $template = HTML::Template->new( filename => 'some.tmpl' );
$template -> param( events => $data );

    [download]
    Boris
[reply]
[d/l]
Re: Pushing a 'hash slice' to an AoH
by Anonymous Monk on Jun 11, 2004 at 16:33 UTC
    A "hash slice" is a list of values from the hash. It is not a hash itself. That means you have to use something a bit more than hash slicing to build the new hash you want to push on your array. It can be done in one statement... 
    push @events,
    { map { $_ => $sqldata->[$i]{$_} } qw( col1 col2 col3 ) };

    [download]
    ...but why?
[reply]
[d/l]
      Thank you Anonymous Monk (that monk gets around). Before the OP, I had tried using map, but was unable to get it to work successfully. Yours worked like a charm.

      —Brad
      "A little yeast leavens the whole dough."
[reply]
Re: Pushing a 'hash slice' to an AoH
by Roy Johnson (Monsignor) on Jun 11, 2004 at 20:35 UTC
    Your last example is equivalent to 
    @{$events[$ctr++]}{'col1','col2','col3'} = @{$sqldata->[$i]}{'col1','c
+ol2','col3'};

    [download]
    if you're inclined to do things slicewise.
    We're not really tightening our belts, it just feels that way because we're getting fatter.
[reply]
[d/l]
Re: Pushing a 'hash slice' to an AoH
by eXile (Priest) on Jun 12, 2004 at 14:22 UTC
    Hi, Maybe I don't understand the reason why you select all columns from your MySQL table. The more easy way to do this to me seems something like: 
    SELECT col1,col2,col3 from sometable where col5 = col8

    [download]
    and then deref the results into your $events structure.
[reply]
[d/l]
[select]
      Helpful monks,
      You and borisz make get points. However, in my actual production code, I do as much as I can with the WHERE as possible, including lots that I didn't show in my stripped-down example in the OP. However, I am testing for such complex relationships in the rest of the code that I have found it impossible to do it all with the SELECT statement. For the sake of posting on SoPW, I distilled my problem down to the bare essentials, which is why it looks like I wasn't taking full advantage of the SQL language. But thanks for the prompting.

      —Brad
      "A little yeast leavens the whole dough."
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom