Getting Record Set via DBI into an Array of Arrays

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

Hello, i would like to create a pie Chart using the GD Module from values that come in via a Database Query. The Graph is drawn from Data that are in the @data array, which should look like for example the following:

@data = (['Siemens','Nokia','Sony','Ericcson','Samsung'],
         [45000,12000,11000,18000,8000]);
[download]

I get my Values from a Database, so the Code for that part is

my $sth = $dbh->prepare("SELECT kunde,umsatz FROM db_kundenumsatz");

my @data;

while (@data = $sth->fetchrow_array()){
#reading the data into an array of arrays just like the above
}
[download]

So my Question is, while i am doing my fetchrow_array, how can i add the Values into my @data array of arrays, so that they are stored exactly like what i wrote above? Eg. so that Data Dumper would output it in this way


# i dont want to write this out by hand, but rather auto
# create it via my Database Recordset that i fetched with
# $sth->fetchrow_array()
@data = (['Siemens','Nokia','Sony','Ericcson','Samsung'],
         [45000,12000,11000,18000,8000]);

print Dumper(@data);

$VAR1 = [
  'Siemens',
  'Nokia',
  'Sony',
  'Ericcson',
  'Samsung'
];
$VAR2 = [
  45000,
  12000,
  11000,
  18000,
  8000
];
[download]

I read up on Array of Arrays, and tried a many ways suggested, but the Data just wont come out in the correct Format. If anyone could show me the way, that would be awesome. Thanks and best regards!

Comment on Getting Record Set via DBI into an Array of Arrays Select or Download Code

Replies are listed 'Best First'.
Re: Getting Record Set via DBI into an Array of Arrays by ikegami (Patriarch) on Apr 13, 2009 at 07:05 UTC
`while (my $row = $sth->fetch()){ push @{ $data[0] }, $row->[0]; push @{ $data[1] }, $row->[1]; }` [download] Note that passing scalars to Data::Dumper produces better results. `print Dumper(\@data);` [download] Update: Fixed missing arrows too.	[reply] [d/l] [select]
Re^2: Getting Record Set via DBI into an Array of Arrays by overrider (Acolyte) on Apr 13, 2009 at 07:30 UTC
Hi there, using `while (my $row = $sth->fetch()){ push @{ $data[0] }, $row[0]; push @{ $data[1] }, $row[1]; } print Dumper(@data);` [download] produced this: `$VAR1 = [ undef, undef, undef, undef, undef ]; $VAR2 = [ undef, undef, undef, undef, undef ];` [download] Using `while (my @row = $sth->fetchrow_array()){ push @{$data[0]}, $row[0]; push @{$data[1]}, $row[1]; } print Dumper(@data);` [download] produced this: `$VAR1 = [ 'Siemens', 'Ericcson', 'Nokia', 'Sony', 'Samsung' ]; $VAR2 = [ '45000', '18000', '12000', '11000', '8000' ];` [download] so the last one there is exactly what i needed! Thanks a lot.	[reply] [d/l] [select]
Re^3: Getting Record Set via DBI into an Array of Arrays by ikegami (Patriarch) on Apr 13, 2009 at 13:44 UTC
Sorry, was tired. I meant `while (my $row = $sth->fetch()){ push @{ $data[0] }, $row->[0]; push @{ $data[1] }, $row->[1]; }` [download] My code would have given you a compile-time error had you used `use strict;`.	[reply] [d/l] [select]
Re: Getting Record Set via DBI into an Array of Arrays by Anonymous Monk on Apr 13, 2009 at 07:38 UTC
`my $sth = $dbh->prepare("SELECT kunde,umsatz FROM db_kundenumsatz"); my (@kunde, @umsatz,$kunde, $umsatz); while (($kunde, $umsatz) = $sth->fetchrow_array()){ push @kunde $kunde; push @umsatz, $umsatz; } my @data=\@kunde,\@umsatz);` [download] I think this is what you want, you could of course use anonymous arrays in @data ie push @data->[0] $kunde... However I left it this way for clarity	[reply] [d/l]
Re: Getting Record Set via DBI into an Array of Arrays by shmem (Chancellor) on Apr 13, 2009 at 09:22 UTC
Use `my @data = @{ fetchall_arrayref() };` [download] to get the recordset as an array containing arrayrefs of rows. See DBI.	[reply] [d/l]
Re^2: Getting Record Set via DBI into an Array of Arrays by ikegami (Patriarch) on Apr 13, 2009 at 14:03 UTC
Aside from the missing object in the method call, that creates an array element per row. The OP wants an array element per column. I suppose you could fix it afterwards using something like `use Algorithm::Loops qw( MapCar ); my @data = MapCar { [ @_ ] } @{ $sth->fetchall_arrayref() };` [download] Note that using `selectall_arrayref` saves you from calling `execute` and even `prepare` if you so desire. `use Algorithm::Loops qw( MapCar ); my @data = MapCar { [ @_ ] } @{ $dbh->selectall_arrayref($sth_or_sql) };` [download]	[reply] [d/l] [select]
Re: Getting Record Set via DBI into an Array of Arrays by bradcathey (Prior) on Apr 13, 2009 at 13:50 UTC
Required reading You will learn some amazing shortcuts: `my $data = $dbh->selectall_arrayref($stmt, {Slice => {}});` [download] —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply] [d/l]
Re^2: Getting Record Set via DBI into an Array of Arrays by ikegami (Patriarch) on Apr 13, 2009 at 14:09 UTC
Nice link and I hadn't see `Slice =>` before, but your code doesn't do what the OP wants. It creates a hash per row. The OP wants an array per column. Everyone's making two errors.	[reply] [d/l]
Re: Getting Record Set via DBI into an Array of Arrays by Anonymous Monk on Apr 13, 2009 at 07:07 UTC
`my @bong; while (@data = $sth->fetchrow_array()){ push @bong, \@data; }` [download]	[reply] [d/l]
Re^2: Getting Record Set via DBI into an Array of Arrays by ikegami (Patriarch) on Apr 13, 2009 at 07:11 UTC
Each element of `@bong` will be the same since you're always taking a reference of the same array. And the data wouldn't be oriented as desired even if you fixed that. And talk about confusing, calling `@bong` the OP's `@data`, and calling `@data` something that isn't the OP's `@data`.	[reply] [d/l] [select]