in reply to Re^2: Getting mysql data into gd
in thread Getting mysql data into gd

Hello, shanta.

An old-fashioned, brute-force way to troubleshoot a problem is to check what is happening at key points in the program. This helps ensure the data you think you should be getting and using is what you are actually getting and using.

For example, this code shows the data returns after every step. This is likely to be helpful in isolating the problem:

#!/usr/bin/perl
use strict;
use warnings;

use DBI;
use GD::Graph::Data;
use GD::Graph::bars;

# do DBI things, like connecting to the database, statement
# preparation and execution

# Don't forget to print the results of your various DBI commands.  
# Your open(), for example, might not be working correctly.

my $sql = "SELECT time, mastuntemp, LineTemp, spargtemp FROM brew_temp
+_tb WHERE sitename = 'Brew' AND batchnumber = '$batchnumber' ORDER BY
+ time";
print "DEBUG:  \$sql = \"$sql\"\n";

my $data = GD::Graph::Data->new();
print "DEBUG:  \$data = \"$data\"\n";

my $sth = $dbh->prepare($sql);
print "DEBUG:  \$sth = \"$sth\"\n";

if (!$sth->execute()) {
    die "Error: ". $sth->errstr ."\n";
}

my @row;
while (@row = $sth->fetchrow_array) {
    print "DEBUG:  \@row = \"@row\"\n";
    my $rowidx = 0;
    foreach my $rowdata (@row) {
        print "  [$rowidx] = \"$rowdata\"\n";
        $rowidx++;
    }
    $data->add_point(@row);
}

exit;

[download]

Replies are listed 'Best First'.
Re^4: Getting mysql data into gd
by shanta (Novice) on Sep 03, 2017 at 17:31 UTC

    There are 4 or more rows of data

    The query works 

    DEBUG:  $sth = "DBI::st=HASH(0x15e6e68)"
DEBUG:  @row = "07:40 62.50 64.00 69.40"
  [0] = "07:40"
  [1] = "62.50"
  [2] = "64.00"
  [3] = "69.40"

    [download]

    We are getting only one row and the fallowing error.

    Can't call method "add_point" on an undefined value at ./graph.cgi line 54.

    Time is the x value and the numbers are the temp. I removed time from the query and it still fails.

[reply]
[d/l]

      Try

      #!/usr/bin/perl
use strict;
use DBI;
use GD::Graph::Data;
use GD::Graph::bars;

my $batchnumber = '20170903nervana';

my $sql = '
  SELECT time, mastuntemp, LineTemp, spargtemp 
  FROM brew_temp_tb 
  WHERE sitename = ? 
    AND batchnumber = ?
  ORDER BY time';

my $dbh = dbh(); # connect
my $sth = $dbh->prepare($sql);
$sth->execute('Brew',$batchnumber);           
           
my $data = GD::Graph::Data->new();
while (my @row = $sth->fetchrow_array){
  $data->add_point(@row);
}

my $chart = GD::Graph::bars->new();
my $gd = $chart->plot($data);
open(IMG, '>','bar.png') or die $!;
binmode IMG;
print IMG $gd->png;

# connect
sub dbh{
  my $database = "test"; 
  my $user     = "user"; 
  my $pw       = "password";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, 
     { RaiseError=>1, AutoCommit=>1 } );
  return $dbh;
}

      [download]
      poj
[reply]
[d/l]

In Section Seekers of Perl Wisdom