Re: Data to fetch from Database to display in graph

[choroba]

I usually use gnuplot directly to draw graphs.

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

use DBI;

my $filename = '1.dat';
my $graph = '1.png';

# Fake the database.
my $db = 'DBI'->connect('dbi:SQLite:dbname=:memory:', "", "");
$db->do('CREATE TABLE goods (id INTEGER, price DECIMAL(5,2), quantity 
+INTEGER)');

my $populate = $db->prepare('INSERT INTO goods (id, price, quantity) V
+ALUES (?, ?, ?)');
$populate->execute(@$_) for [ 1, 10,   100 ],
                            [ 2,  2.5, 150 ],
                            [ 3, 12.2,  25 ];

# Extract the ranges.
my $get_maxq = $db->prepare('SELECT MAX(quantity), MAX(price) FROM goo
+ds');
$get_maxq->execute;
my ($maxq, $maxp) = $get_maxq->fetchrow_array;

# Populate the input file.
open my $data, '>', $filename or die $!;

my $extract = $db->prepare('SELECT price, quantity FROM goods');
$extract->execute;
while (my @row = $extract->fetchrow_array) {
    print {$data} "@row\n"
}
close $data;

# Draw the graph.
open my $gp, '|-', 'gnuplot' or die $!;
print {$gp} << "__GP__";
set term png;
set output '$graph';

set yrange [0:$maxq * 1.1];
set y2range [0:$maxp * 1.1];
set ytics nomirror;
set y2tics 0, 2.5;
set xtics 0, 1 out;
set key out;

plot '$filename' using 0:(\$1 * $maxq / $maxp):(.9) with boxes \\
    fillstyle transparent solid .1 title 'histogram',\\
    '' using 0:2 with lines fillcolor 2 title 'lines';
__GP__

close $gp;
unlink $filename;