in reply to Re: Re: GD::Graph::lines problem!
in thread GD::Graph::lines problem!

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.

Replies are listed 'Best First'.
Re: Re: Re: Re: GD::Graph::lines problem!
by Baiul (Acolyte) on May 11, 2003 at 10:08 UTC
    First off thanks for the help!

    Second, I found a way around the initial way I was getting the values of table names as follows 

    @dates = $dbh->ListTables;

    [download]

    This brings out the data without problem but makes no difference to the end result.

    In regards to me doing the wrong thing with 

    @data = [\@dates, \@rage]

    [download]

    I thought this myself, but when I type in the data for two arrays and then add them in the same fashion as show above it works. Only if the original data comes from Mysql do I seem to have a problem, be it tablename or results from the query.

    I'm going to try the rest of the script as you have it and let you know how it went.

    Thanks again,
    Baiul.

    Ubi Concordia Ibi Victoria

[reply]
[d/l]
[select]
      Tonight I will at last sleep well! Your code failed and my code failed but a hybrid of the two works perfectly! Ubi Concordia Ibi Victoria indeed! The code below works without problem and prints out the graph that I am ever so happy to see!

      Thank you so much again for your help! Code as follows: 

      #!/usr/bin/perl


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

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

my $dbh = Mysql->connect('localhost','pzbg001','root','xxxxxxx');

@dates = $dbh->ListTables;




# 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 $dbh = DBI->connect('DBI:mysql:pzbg001', 'root', 'xxxxxxx', {Ra
+iseError => 1});
    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 (@total, $octets/(1024*1024));
}

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




#####################

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;

      [download]

      Baiul

      Ubi Concordia Ibi Victoria

[reply]
[d/l]

In Section Seekers of Perl Wisdom