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

Help!

I am trying to use GD::Graph using data returned from a DBI call. But the layout of the data is not what the GRAPH module is expecting. DBI returns (r1c1, r1c2, r1c3) (r2c1, r2c2, r2c3), etc... and the graph module is expecting (r1c1, r2c2...)(r1c2, r2c2...)

Here is my code so far...

#! perl -w
use CGI ':standard'; 
use GD::Graph::bars; 
use DBI;

my $SQL = "SELECT A.YM, SUM(A.TC)FROM A GROUP BY A.YM ORDER BY A.YM
";

$dbh = DBI->connect('dbi:DB2:DB2W',"$mf_id" , "$mf_pw"); if ($DBI::err
+str) {print "$DBI::errstr";}
$sth = $dbh->prepare($SQL);                              if ($DBI::err
+str) {print "$DBI::errstr";}
$rc  = $sth->execute;                                    if ($DBI::err
+str) {print "$DBI::errstr";}
$data = $sth->fetchall_arrayref;

my $mygraph = GD::Graph::bars->new(800, 500);
$mygraph->set(      
    x_label     => 'Month',
    y_label     => 'Transactions',
    title       => 'Number of Transactions per Month',
    show_values => 1,
    bar_spacing => 10,
) or warn $mygraph->error;

my $myimage = $mygraph->plot(\@$data) or die $mygraph->error;
print "Content-type: image/png\n\n";
print $myimage->png;

[download]


Thanks for the Help!

Edit:larsen, changed title, added <code> tags and fixed formatting.

Replies are listed 'Best First'.
Re: GD::GRAPH using DBI Data.
by gmax (Abbot) on Feb 07, 2003 at 16:05 UTC
    You can get all the records at once with $dbh->selectall_arrayref($query) and then rotate the resulting matrix.
    Tested code ahead.
    #!/usr/bin/perl -w
use DBI;
use strict;

my $dbh = DBI->connect("DBI:mysql:test;host=localhost"
        . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
        undef, undef, {RaiseError => 1})
        or die "can't connect\n";

my $query = qq{SELECT * from testgraph};
my $result = $dbh->selectall_arrayref($query);

print "straight\n";
my @rotated =();
for my $row (@$result) {
    print "@$row\n";
    for (0.. scalar @$row -1) {
        push @{$rotated[$_]}, $row->[$_];
    }
}
print "rotated\n";
for my $row( @rotated) {
    print "@$row\n";
}

$dbh->disconnect();
__END__

$ mysql -t -e "select * from test.testgraph"
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |   10 |  100 |
|    2 |   20 |  200 |
|    3 |   30 |  300 |
|    4 |   40 |  400 |
+------+------+------+

$ perl testgraph.pl
straight
1 10 100
2 20 200
3 30 300
4 40 400
rotated
1 2 3 4
10 20 30 40
100 200 300 400

    [download]
     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
      THANKS! That was exactly what I needed !


      John Herbold
[reply]
Re: GD:GRAPH using DBI Data.
by Mr. Muskrat (Canon) on Feb 07, 2003 at 15:29 UTC

    How about pushing your data into two seperate arrays?

    Untested code follows:

    #! perl -w
use CGI ':standard';
use GD::Graph::Bars;
use GD::Graph::Data;
use DBI;

my ($ym, $tc, @ym, @tc);

my $SQL = "SELECT A.YM, SUM(A.TC)FROM A GROUP BY A.YM ORDER BY A.YM ";
my $dbh = DBI->connect('dbi:DB2:DB2W',"$mf_id" , "$mf_pw", { PrintErro
+r => 0}) || die $DBI::errstr);
my $sth = $dbh->prepare($SQL) or die $dbh->errstr;
$sth->execute() or die $sth->errstr;
$sth->bind_columns(undef, \$ym, \$tc);
while ($sth->fetch) {
  push(@ym, $ym);
  push(@tc, $tc);
}
my $data = GD::Graph::Data->new([\@ym, \@tc]);
my $values = $data->copy;

my $mygraph = GD::Graph::bars->new(800, 500);
$mygraph->set(
x_label => 'Month',
y_label => 'Transactions',
title => 'Number of Transactions per Month',
show_values => 1,
bar_spacing => 10,
) or warn $mygraph->error;

my $myimage = $mygraph->plot($data) or die $mygraph->error;
print "Content-type: image/png\n\n";
print $myimage->png;

    [download]

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom