Variables as a variable name... (I know you shouldn't but...)

[2mths]

The 'problem' I'm trying to solve (probably trivial, but not for me):

A database query returns results like:

DayOfWeek	ProductionLine	Output
2	CANNING	18353
2	MULTIPACK	14878
2	QUEST	911
3	CANNING	46775
3	MULTIPACK	42601
3	QUEST	1564
4	CANNING	81302
4	MULTIPACK	67542
4	QUEST	1879

These are presented to the rest of my script as $results[0], $results1 and $results2.

Ultimately I want to get this data into several arrays
@dayofweek, which would contain ('2', '3', '4')
@canning, which would contain ('18353', '46775' etc)
@multipack and @quest (I'm sure you get the picture).

Then I think I can do @data_to_plot = ( [@dayofweek], [@canning], [@multipack], [@quest] ); which I can happily feed into GD::Graph to get a pretty graph drawn for me.

I could achieve what I need to by doing three separate queries of the database and pushing results into a separate array each time. Or a lot of if, elsif, else pattern matching. Indeed the former is what I'm currently doing. However I'm sure there is a 'better' way.

A way, but I am led to understand a bad way, of achieving this would be to try and push the Output figure, $results2, for each line of results into an array which has as it's name the value of the Production Line, $results1. I think this is possible, though I haven't managed to work out the syntax (despite a good look through Chapter 9, Data Structures, of Programming Perl). However in searching online for an example of the syntax I came across a copy of a newsgroup post about why naming variables with other variables is a bad idea.

Thus I have journeyed to prostrate myself before the gates of the Monastery to ask for any drops of wisdom that a passing monk might care to pass my way.

[broomduster]

Hashes are a good way to have what you want. The data you show can be easily collected into a multi-layer hash, with ProductionLine as the top-level key, DayOfWeek as the second-level key, and Output as the value. (You can flip the top- and second-layer keys if it is more convenient to think of the data that way; I like the ProductionLine at the top, especially if you are going to end up plotting based on Line).

The code snippet below should get you started.

You should also have a look at perldsc and perlreftut for some basic training in complex data structures in Perl.

use strict;
use warnings;
use Data::Dumper;

my %outputs;

while ( <DATA> ) {
    my($day, $line, $output) = (split);
    $outputs{$line}{$day} = $output;
}

print Dumper(\%outputs);

__END__
2    CANNING     18353
2    MULTIPACK    14878
2    QUEST    911
3    CANNING    46775
3    MULTIPACK    42601
3    QUEST    1564
4    CANNING    81302
4    MULTIPACK    67542
4    QUEST    1879

---

Output:

$VAR1 = {
          'CANNING' => {
                         '4' => '81302',
                         '3' => '46775',
                         '2' => '18353'
                       },
          'QUEST' => {
                       '4' => '1879',
                       '3' => '1564',
                       '2' => '911'
                     },
          'MULTIPACK' => {
                           '4' => '67542',
                           '3' => '42601',
                           '2' => '14878'
                         }
        };

[Skeeve]

To be honest, I don't quite understand your request.

You want to loop over your query results and push each column's value into a seperate array?

So the solution should be to push them into an Hash of Arrays (HoA) like this:

# untested
my %results;
while (my $data = $sth->fetchrow_hashref()) {
    while (my($k, $v)= each %$data) {
        push( @{$results{$k}}, $v );
    }
}
# your dataplot should then go like this:
@data_to_plot = ( [ @results{qw/dayofweek canning multipack quest/} ] 
+);

[Bloodnok]

If I understand you correctly, you're after a pseudo-merge of the 3, row-aligned, arrays contained within @results - where ...
• $results[0] contains the DayOfWeek column
• $results[1] contains the ProductionLine column
• $results[2] contains the Output column
• Each of the above arrays is the same size.
• For each block in the ProductionLine array, the order of entries is always the same e.g. alphabetically ordered

If so, then maybe something along the following (untested) lines might suffice ...

use strict;
use warnings;

use Data::Dumper;

my @results;
while (<DATA>) {
        my @row = split;
        for (my $idx = 0; $idx < 3; $idx++) {
                push @{ $results[$idx] }, $row[$idx];
        }
}

# Assume @results already populated...
my ($day_of_week, $production_line, $output) = @results;
my @data_to_plot;

# Ignore the headers
for (my $idx = 0; $idx < 3; $idx++) { shift @{ $results[$idx] } }

###
# Extending an idea from [http://www.perl.com/doc/FAQs/FAQ/oldfaq-html
+/Q5.4.html]...
my %saw;
my %production_lines = ( map { $_ => [] } grep ! $saw{$_}++, @$product
+ion_line );
#
###

for (my $index = 0; $index < @$day_of_week; $index++) {
    push @{ $data_to_plot[0] }, $day_of_week->[$index] unless
        grep /^$day_of_week->[$index]$/, @{ $data_to_plot[0] };

    push @{ $production_lines{$production_line->[$index]} }, $output->
+[$index];
}

push @data_to_plot, ( map { $production_lines{$_} } sort keys %product
+ion_lines );

print Dumper \@data_to_plot;

__DATA__
DayOfWeek       ProductionLine  Output
2       CANNING 18353
2       MULTIPACK       14878
2       QUEST   911
3       CANNING 46775
3       MULTIPACK       42601
3       QUEST   1564
4       CANNING 81302
4       MULTIPACK       67542
4       QUEST   1879

.
.

results in...

!perl fred.pl
$VAR1 = [
          [
            '2',
            '3',
            '4'
          ],
          [
            '18353',
            '46775',
            '81302'
          ],
          [
            '14878',
            '42601',
            '67542'
          ],
          [
            '911',
            '1564',
            '1879'
          ]
        ];

Once more, sits back and awaits flames for bad code, style, karma etc...

Update:
Corrected typos
Tested - corrected 'features', added results

HTH ,

[injunjoel]

Greetings,
Assuming you are using DBI have a look at this method to get what you want. As far as I can tell from your post that should do it.

-InjunJoel

[Skeeve]

Hey! That looks GREAT! ++ & Thanks for telling us. I never noticed that when scanning through perldoc DBI

[2mths]

Thank you broomduster, Bloodnok & Skeeve

Between your responses I have managed to create (an admittedly still messy and improvable) a solution to the 'problem' set.

broomduster - I found the explanation and words that accompanies your snippet as helpful as the snippet itself. I still have no idea how my($day, $line, $output) = (split); works, I haven't managed to emulate it in my code, but by being bit more long winded I was able to emulate the next line and learn how I might populate a Hash of Hashes. (I have started reading through the first link you posted and will get onto the second.)

Skeeve - Short and sweet. I'm afraid to say that even in that short snippet you lost me in the while loop but this:


# your dataplot should then go like this:
@data2plot = ( [ @results{qw/dayofweek canning multipack quest/} ] );

was really helpful for showing me how to package my data ready to feed into GD::Graph.

Bloodnok - I'm afraid most of your example code went a long way over my head. Still knowing what it does and what the output looks like I'm hoping I can still make use of it to more eloquently solve my 'problem' (the same can be said for the code of the others that I don't yet understand.)

It seems only reasonable to post (for ridicule if nothing else) my code. So abbreviating the bits that don't really matter...

#! c:/perl/bin/perl.exe -w
use GD::Graph::bars;

#having connected to the database and composed a query, $query

my %outputs;

$db->Sql("$query"); 
while ($db->FetchRow()) { 
    @results = $db->Data ;
        $day = $results[0] ;
        $line = $results[1] ;
        $output = $results[2] ;

        $outputs{$line}{$day} = $output;
    }

# create individual arrays of data
foreach $key (keys %outputs) {
    foreach $key2 (sort keys %{ $outputs{$key}}) {
        print "key: $key \t key2: $key2 \t data: $outputs{$key}{$key2}
+ \n";
        push( @{$key}, $outputs{$key}{$key2});
    
        $repeateddays{$key2}=$key2;
    }
}

# a hash with my days in (not very clever but neither am I)
@days = (sort keys %repeateddays);

# A visual check things have worked
print "At canning = @CANNING \t quest = @QUEST \t MULTIPACK = @MULTIPA
+CK \t days = @days\n";

# Put the data into a format GD::Graph likes
@datatoplot = ( [@days], [@CANNING] );

# Set up and draw my graph
my $mygraph3 = GD::Graph::bars->new(400, 250);
$mygraph3->set( # some stuff ) or warn $mygraph3->error;

my $myimage = $mygraph->plot(\@datatoplot) or die $mygraph3->error;
    
$filename = "multi_layer_hash_development.png";
open (PRODGRAPHCUMU, ">c:/perl/bin/Durge/GD/" . $filename) ; 

# Make sure we are writing to a binary stream
binmode PRODGRAPHCUMU;
    
print PRODGRAPHCUMU $myimage->png;

exit 0;
# Voila I have a pretty graph!

If you've read through that you'll probably be wincing at a number of things:
a) Having gone to the effort to do everything in a Hash of Hashes (I think) I've then gone and manually created arrays from this b) I've only actually drawn a graph of one of the datasets c) How little you perceive I've learned and done from your collective postings.

All I can say is that now I have something that works I will try to improve it, in a variety of ways. I have actually spent much of my day (all the time I've been at my desk) working on this - so despite it's simple appearance, actually quite a chunk of time and effort has gone in and I intend to spend tomorrow on it too (certainly beats working).

So again... Thankyou all.

[Skeeve]

I guess you meant the inner while loop?

while (my $data = $sth->fetchrow_hashref()) {
    # %$data is now a hash.
    # keys are the column's names
    # values are the column's values
    while (my($k, $v)= each %$data) {
        # Now I loop over each column-value pair
        push( @{$results{$k}}, $v );
        # pushing into the results' hashes all the values.
        # So $result{'canning'} is a reference to an array
        # for all the canning-values etc.pp.
    }
}