in reply to Getting mysql data into gd

Hello shanta,

Welcome to the Monastery. Well I am not sure why you want to export your data perldsc/ARRAYS OF ARRAYS when the module GD::Graph::Data loads the data directly into the plot.

Sample code from the GD::Graph::Data/EXAMPLES:

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

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

my $data = GD::Graph::Data->new();

while (@row = $sth->fetchrow_array)
{
    $data->add_point(@row);
}

my $chart = GD::Graph::bars->new();
my $gd = $chart->plot($data);

[download]

So in your case if you are sure the data that you exporting is the necessary data from the plot something like that should work out of the box (code untested):

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

my $data = GD::Graph::Data->new();

my $sth = $dbh->prepare("SELECT time, mastuntemp, LineTemp, spargtemp 
+FROM brew_temp_tb WHERE sitename = 'Brew' AND batchnumber = '$batchnu
+mber' ORDER BY time";");

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

my @row;
while (@row = $sth->fetchrow_array) {
    $data->add_point(@row);
}

[download]

We can not test your code, be we need a minimum working sample of code that replicates the table and some demo data to insert for experimentation purposes. Alternatively you can test it and let us know if it worked.

Looking forward to your update, BR.

Seeking for Perl wisdom...on the process of learning...not there...yet!

Replies are listed 'Best First'.
Re^2: Getting mysql data into gd
by shanta (Novice) on Aug 28, 2017 at 02:10 UTC

    Thanks for the code and the link to the sample code

    I get this error

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

    guessing the query did not work.

[reply]

      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]

[reply]
[d/l]

        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]

      Hello shanta,

      It looks like the data that you are trying to export from the DB they are not populated. At this point without any sample of data and sample of DB table we can only assume that the SELECT function is not populating the data.

      If I was you I would follow the advice of fellow monk marinersk, you need to debug step by step your connection to the DB and that you are actually retrieving the expected data. As soon you have reached this point and you are sure that all the mandatory steps are met then make sure the data that you are providing to GD::Graph::Data are the ones that it expects.

      Sample of expected data taken from the GD::Graph::Data/DESCRIPTION:

      An object of this class contains a list of X values, and a number of l
+ists of corresponding Y values. This only really makes sense if the Y
+ values are numerical, but you can basically store anything.

      [download]

      Make sure that the data that you are trying to push into the Graph are as described by the DESCRIPTION.

      Hope this helps, BR.

      Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]

        Got things down to the query 

        my $batchnumber = $CGI ->param('batchnumber')||"20170903nervana" ;

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

        [download]

        There is data in the database. Which I display in ttml

[reply]
[d/l]
      guessing the query did not work

      Don't guess, test for errors. Or, even better, make DBI do that for you:

      my $dbh=DBI->connect($dsn,$user,$password,{ RaiseError => 1, ... });

      [download]

      Alexander

      --
      Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
[d/l]
        Thanks This failed with the ... had to remove. dose report all dbi errors or just connect?
[reply]

        Tried that code but it failed had to remove ', ...' to make it work.

        Dose this way of requesting and error work for all errors or just connect? As it is in connect.

[reply]

In Section Seekers of Perl Wisdom