CodeWarrior's Cruft

So I had to quickly whip up some graphs today at work based on values coming in from one of our database tables. Nothing better then Perl and GD (GD::Graph) for a quick and effective solution.

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

my $db_host = 'XXXXXX';
my $db_name = 'XXXXXX';   
my $db_user = 'XXXXXX';
my $db_pass = 'XXXXXX';
my $query   = "select month, undelivered from XXXXX where XXXXX";

# create labels and values for x-axis

my ($labels, $values) = get_data_from_sql($db_host, $db_name, $db_user, $db_pass, $query);

# graph and save the output

graph($labels, $values, "Month", "Undelivered", "Undelivered Messages by Month", "undelivered.png");


sub get_data_from_sql($$$$$)
{
  my ($db_host, $db_name, $db_user, $db_pass, $query) = @_;

  my $dbh = DBI->connect("dbi:mysql:$db_name:$db_host", $db_user, $db_pass)
    or die "Couldn't connect to database: " . DBI->errstr;

  my $sth = $dbh->prepare($query)
    or die "Couldn't prepare statement: " . $dbh->errstr;

  $sth->execute();
  my @row= undef;

  my @labels = ();
  my @values = ();

  while (@row = $sth->fetchrow_array())
  {
    push @labels, $row[0];
    push @values, $row[1];
  }

  $dbh->disconnect;

  return (\@labels, \@values);
}

sub graph($$$$$$)
{
  my ($labels, $values, $x_label, $y_label, $title, $out_file) = @_;

  my $data = GD::Graph::Data->new([$labels, $values,])
    or die GD::Graph::Data->error;

  my $my_graph = GD::Graph::bars->new();

  $my_graph->set(
  x_label => $x_label,
  y_label => $y_label,
  title   => $title,
  bar_spacing => 8,
  shadow_depth => 4,
  shadowclr => 'dred',
  transparent => 0,
  )
  or warn $my_graph->error;

  $my_graph->plot($data) or die $my_graph->error;
  my $gd = $my_graph->plot($data) or die $my_graph->error;
  open(IMG, ">$out_file") or die $!;
  binmode IMG;
  print IMG $gd->png;
}

If you do not want your data coming from an SQL statement simply fill in $labels and $values with anything you want, like:

$labels = ['Monday', 'Tuesday', 'Wednesday'];
$data = [45, 66, 89];

Pretty straight forward and handy. the graph looks like this.