Apart from that, I wondered why, in the "fetchrow_array" loop, you were pushing stuff onto an array called "@rage", but then never used that array later in the script. (The only thing passed to the "plot" method is @data, and nothing was done to include @rage as part of @data. Is that part of the problem?

Hi Graff,

Yeah I butchered the code more than a little as I tried to test it. I simplified it again below. The reason I use @rage (symbolic name for my state of mind haha) is that I am not sure how to put the data from the mysql lookup into the 2d array without first collecting them seperately and then pushing them into the same array. (I deleted that line ---my @data = ( \@dates, \@rage);---accidentally when I last posted).

The data that I get is as follows:

+-------------------+
| Tables_in_pzbg001 |
+-------------------+
| 2003_5_10         |
| 2003_5_5          |
| 2003_5_6          |
| 2003_5_7          |
| 2003_5_8          |
| 2003_5_9          |
+-------------------+
[download]

and I realise that the way I strip it down is very messy indeed, I'm not the best perl programmer to say the least :(. Any advice would be more than welcome and thanks again for taking the time to help me out.

#!/usr/bin/perl


use CGI ':standard';
use GD::Graph::lines;
use DBI;
use Data::Dumper;

$inint = "1";
$result = "sum(octets)";

@dates = `mysqlshow --password=\'xxxxxxx\' pzbg001`;
shift @dates;
shift @dates;
shift @dates;
shift @dates;
pop @dates;

foreach $date(@dates){

        chomp $date;
        $date =~ s/ //g;
        $date =~ s/\|//g;
        $date =~ s/\-//g;
        $date =~ s/Tables//g;
        $date =~ s/\+//g;



# Connect to the database.
my $dbh = DBI->connect('DBI:mysql:pzbg001', 'root', 'xxxxxxx', {RaiseE
+rror => 1});


$query = "select " .  "$result "
    . "from " . "$date " . "where" . " inintf = '$inint'";



$sth = $dbh->prepare($query);
if (!$sth) { die "Illegal query: $query" };
$sth ->execute;
while (my @row = $sth->fetchrow_array) {

    foreach $row(@row){
        chomp $row;
        $total = sprintf "%0.f", $row/1024/1024;
        $total += 0;
        push (@rage, $total);

            }
        }
$sth->finish;
}

my @data = ( \@dates, \@rage);

my $mygraph = GD::Graph::lines->new(600, 300);
$mygraph->set(
x_label => 'Date',
y_label => 'Kb of Traffic',
title => 'Traffic for $router ',


# Draw datasets in 'solid', 'dashed' and 'dotted-dashed' lines
line_types => [1],

# Set the thickness of line
line_width => 2,

# Set colors for datasets
dclrs => ['blue'],

) or warn $mygraph->error;
$mygraph->set_legend_font(GD::gdMediumBoldFont);
$mygraph->set_legend('Total');
my $myimage = $mygraph->plot(\@data) or die $mygraph->error;
print "Content-type: image/png\n\n";
print $myimage->png;

__END__
[download]

Baiul

Ubi Concordia Ibi Victoria

Well, now we have a few things to work on... (since you asked for general pointers as well as the help on the main problem, I'll go on at length).

First, there has to be a better way to do this:

@dates = `mysqlshow --password=\'xxxxxxx\' pzbg001`;
shift @dates;
shift @dates;
shift @dates;
shift @dates;
pop @dates;

foreach $date(@dates){
        chomp $date;
        $date =~ s/ //g;
        $date =~ s/\|//g;
        $date =~ s/\-//g;
        $date =~ s/Tables//g;
        $date =~ s/\+//g;
...
[download]

I'd prefer a query that asks for names of tables (I don't know mysql so much, but in oracle, I'd do "select table_name from user_tables" -- 'user_tables' is an oracle-internal table, the same on every server, that lists information on all the user tables in the current user account). If you really want to do this via a back-tick command, and your table names are always numeric dates with underscores, try:

@dates = map { /^\|\s+([\d_]+)\s+\|$/ } `mysqlshow --...`
[download]

(For that matter, I'd use "@tables" as the array name there.)

Next, building the query is simpler than you seem to think:

   $query = "select $result from $date where inintf=$inint";
[download]

Now, I was about to say you have the "sth->finish" call inside the while loop instead of outside (where it belongs), but then I realized that your indentation is still out of whack, making it harder to understand the code.

Another thing that's a little misleading here is that you are, in effect, running the same query on several tables, and each time you run it, you get exactly one scalar value, but you use a "while" loop with a nested "foreach" loop to retrieve that single value.

And finally, getting to the real problem, you are probably doing the wrong thing with @data = [\@dates, \@rage] -- the plot function probably wants an array of data points to graph, where each point is an array having one X coord.value and one Y coord.value (but I don't know GD at all -- you should check the docs on that). Anyway, if my guess is right, you should try it like this (this is the whole thing of doing the queries and assembling the array for plotting):

# get the list of tables (table names are \d+_\d+_\d+):
my @dates = map { /^\|\s+([\d_]+)\s+\|$/ } `mysqlshow --...`

# query each table for sum of traffic,
# push table_name (i.e. date) and traffic sum onto @data:
my @data = ();
for my $date ( @dates ) {
    my $query = "select $result from $date where inintf=$inint";
    my $sth = $dbh->prepare($query);
    if (!$sth) { die "Illegal query: $query" };
    $sth->execute;
    my $octets = ($sth->fetchrow_array)[0]; # don't chomp it!
    $sth->finish;
    push @data, [ $date, $octets/(1024*1024) ];
}

# do all that other GD stuff... then:

my $myimage = $mygraph->plot(\@data) or die $mygraph->error;

# and on to the web page...
[download]

update: added some commentary to the code; also added some more "my" scopers.