Multidimensional arrays and DB columns

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

Hi,

I have a DBI call which returns three colums as array references (oracle) $row->[0] etc. What i would like to do is keep the first value in $row->[0] matched up to the first value in $row->[1] and matched up to the first value in $row->[2], if that makes sense.

What i have so far is:

$sth_ideas->bind_param_inout(":l_proj",\$projects,0,{ora_type=>ORA_RSE
+T});
    $sth_ideas->execute;
    
    while ($row = $projects->fetchrow_arrayref){
        
        $id=$row->[0];
        $name=$row->[1];
        $perm=$row->[2];
        
    push @proj, $id,$name,$perm;
                
    }
    return \@proj;
[download]

Note this is returning an array reference from a subroutine.

Then in another class, i have:

my $count=0;
foreach my $e (@$aref){
$count++;
if ($count==4){
    $count=1; print "\n";
}

print $e,"\t";

}
[download]

which reads each group of 3 array ref element and prints then in a row (tab seperated). What i need is to be able to do the same thing, but also get at the id value for a particular row (which will be passed to another sub) from a GUI. I think i need a multidimensional array or some such construct, but cant get my head round it.

I hope this is clear enough and if anyone has any ideas - they will be appreciated.

thanks -- Joe.

Comment on Multidimensional arrays and DB columns Select or Download Code

Replies are listed 'Best First'.
Re: Multidimensional arrays and DB columns by johngg (Canon) on Dec 12, 2008 at 16:40 UTC
If I understand you correctly, I think you might be better off returning a reference to an array of arrays so you can keep each row's id, name and perm separate. Something like (not tested) `... my $raResults = []; while( $raRow = $projects->fetchrow_arrayref() ) { push @$raResults, [ @{ $raRow }[ 0, 1, 2 ] ]; } return $raResults; ...` [download] and later print each set of three items, locally setting the list separator (see perlvar) to a tab `foreach my $raRowPick ( @$raResults ) { print do{ local $" = qq{\t}; qq{@$raRowPick\n}; }; }` [download] I hope this is useful. Cheers, JohnGG Update: just noticed a missing closing square bracket on the `push` line, now corrected. Update 2: I also realise that i didn't explicitly answer you with regard to getting at an ID for a particular row. We could expand the printing loop with something along these lines. `foreach my $raRowPick ( @$raResults ) { print do{ local $" = qq{\t}; qq{@$raRowPick\n}; }; if( $raRowPick->[ 0 ] eq $theIdWeWant ) { doSomethingWith( $raRowPick[ 0 ] ); } }` [download]	[reply] [d/l] [select]
Re: Multidimensional arrays and DB columns by mje (Curate) on Dec 12, 2008 at 16:30 UTC
Won't fetchall_arrayref give you what you want? It gives you an array reference where each element is a row and an array reference to the row data (columns). i.e. a multidimensional array with the first dimension being the row and the second being the columns. If you want it by some id look at fetchall_hashref.	[reply]