In this example, we will introduce a technique where the database query is performed in the containing HTML page, and the data is passed to the ChartDirector script as HTTP GET query parameters. Using this method, it is possible to perform one database query and feed the data to multiple charts.
The user will select a year from a HTML form and press OK. The web server will query a database to obtain the necessary data, and return a web page containing the bar chart and the line chart for the selected year.
The code for producing the HTML form is listed below.
The code outputs a drop down select list to allow the user to select a year. Based on the selected year, it queries the database and read the data into arrays. The data are then serialized into comma delimited strings using the "join" function. Two <IMG> tags are used to invoke two scripts for charts generation, where the data are passed in as HTTP GET query parameters using the comma delimited strings.
[CGI Version] perldemo_cgi\dbdemo2.pl
#!/usr/bin/perl
# In the sample code, the ChartDirector for Perl module is assumed to be in "../lib"
use File::Basename;
use lib (dirname($0)."/../lib") =~ /(.*)/;
use perlchartdir;
# Get HTTP query parameters
use CGI;
my $query = new CGI;
# The currently selected year
my $selectedYear = ($query->param("year") or 2001);
# SQL statement to get the monthly revenues for the selected year.
my $SQL =
"Select Software, Hardware, Services From revenue Where Year(TimeStamp) = $selectedYear Order ".
"By TimeStamp";
#
# Connect to database and read the query result into arrays
#
use DBI;
my $dbh = DBI->connect('dbi:mysql:sample', 'test', 'test');
my $sth = $dbh->prepare($SQL);
$sth->execute();
my $software = [];
my $hardware = [];
my $services = [];
while (my @row = $sth->fetchrow_array) {
push @$software, $row[0];
push @$hardware, $row[1];
push @$services, $row[2];
}
$dbh->disconnect;
# Serialize the data into a string to be used as HTTP query parameters
my $httpParam = sprintf("year=%s&software=%s&hardware=%s&services=%s", $selectedYear, join(",", @
$software), join(",", @$hardware), join(",", @$services));
#
# The following code generates the <option> tags for the HTML select box, with the <option> tag
# representing the currently selected year marked as selected.
#
my $optionTags = [(undef) x (2001 - 1990 + 1)];
for(my $i = 1990; $i < 2001 + 1; ++$i) {
if ($i == $selectedYear) {
$optionTags->[$i - 1990] = "<option value='$i' selected>$i</option>";
} else {
$optionTags->[$i - 1990] = "<option value='$i'>$i</option>";
}
}
my $selectYearOptions = join("", @$optionTags);
print "Content-type: text/html\n\n";
print <<EndOfHTML
<html>
<body style="margin:5px 0px 0px 5px">
<div style="font-size:18pt; font-family:verdana; font-weight:bold">
Database Integration Demo (2)
</div>
<hr style="border:solid 1px #000080" />
<div style="font-size:10pt; font-family:verdana; width:600px">
This example demonstrates creating a chart using data from a database. The database
query is performed in the containing HTML page. The data are then passed to the chart
generation pages as HTTP GET parameters.
<ul>
<li><a href="viewsource.pl?file=$ENV{"SCRIPT_NAME"}">
View containing HTML page source code
</a></li>
<li><a href="viewsource.pl?file=dbdemo2a.pl">
View chart generation page source code for upper chart
</a></li>
<li><a href="viewsource.pl?file=dbdemo2b.pl">
View chart generation page source code for lower chart
</a></li>
</ul>
<form>
I want to obtain the revenue data for the year
<select name="year">
$selectYearOptions
</select>
<input type="submit" value="OK">
</form>
</div>
<img src="dbdemo2a.pl?$httpParam">
<br><br>
<img src="dbdemo2b.pl?$httpParam">
</body>
</html>
EndOfHTML
; |
The <IMG> tags in the above code invoke "dbdemo2a.pl" and "dbdemo2b.pl" for charts generation. In "dbdemo2a.pl" and "dbdemo2b.pl", the data is retrieved from the HTTP GET query parameters and deserialized back into arrays by using the "split" function. The data are then used to generate the charts.
[CGI Version] perldemo_cgi\dbdemo2a.pl
#!/usr/bin/perl
# In the sample code, the ChartDirector for Perl module is assumed to be in "../lib"
use File::Basename;
use lib (dirname($0)."/../lib") =~ /(.*)/;
use perlchartdir;
# Get HTTP query parameters
use CGI;
my $query = new CGI;
#
# Retrieve the data from the query parameters
#
my $selectedYear = $query->param("year");
my $software = [split(",", $query->param("software"))];
my $hardware = [split(",", $query->param("hardware"))];
my $services = [split(",", $query->param("services"))];
#
# Now we obtained the data into arrays, we can draw the chart using ChartDirector
#
# Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee) background, black
# border, 1 pixel 3D border effect and rounded corners.
my $c = new XYChart(600, 300, 0xeeeeee, 0x000000, 1);
$c->setRoundedFrame();
# Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to white (ffffff)
# and border and grid colors to grey (cccccc)
$c->setPlotArea(60, 60, 520, 200, 0xffffff, -1, 0xcccccc, 0xccccccc);
# Add a title to the chart using 15pt Times Bold Italic font, with a light blue (ccccff) background
# and with glass lighting effects.
$c->addTitle("Global Revenue for Year $selectedYear", "timesbi.ttf", 15)->setBackground(0xccccff,
0x000000, perlchartdir::glassEffect());
# Add a legend box at (70, 32) (top of the plotarea) with 9pt Arial Bold font
$c->addLegend(70, 32, 0, "arialbd.ttf", 9)->setBackground($perlchartdir::Transparent);
# Add a line chart layer using the supplied data
my $layer = $c->addLineLayer2();
$layer->addDataSet($software, 0xff0000, "Software")->setDataSymbol($perlchartdir::CircleShape, 9);
$layer->addDataSet($hardware, 0x00ff00, "Hardware")->setDataSymbol($perlchartdir::DiamondShape, 11);
$layer->addDataSet($services, 0xffaa00, "Services")->setDataSymbol(perlchartdir::Cross2Shape(), 11);
# Set the line width to 3 pixels
$layer->setLineWidth(3);
# Set the x axis labels. In this example, the labels must be Jan - Dec.
my $labels = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec"];
$c->xAxis()->setLabels($labels);
# Set the y axis title
$c->yAxis()->setTitle("USD (Millions)");
# Set axes width to 2 pixels
$c->xAxis()->setWidth(2);
$c->yAxis()->setWidth(2);
# Output the chart in PNG format
binmode(STDOUT);
print "Content-type: image/png\n\n";
print $c->makeChart2($perlchartdir::PNG); |
[CGI Version] perldemo_cgi\dbdemo2b.pl
#!/usr/bin/perl
# In the sample code, the ChartDirector for Perl module is assumed to be in "../lib"
use File::Basename;
use lib (dirname($0)."/../lib") =~ /(.*)/;
use perlchartdir;
# Get HTTP query parameters
use CGI;
my $query = new CGI;
#
# Retrieve the data from the query parameters
#
my $selectedYear = $query->param("year");
my $software = [split(",", $query->param("software"))];
my $hardware = [split(",", $query->param("hardware"))];
my $services = [split(",", $query->param("services"))];
#
# Now we obtained the data into arrays, we can draw the chart using ChartDirector
#
# Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee) background, black
# border, 1 pixel 3D border effect and rounded corners.
my $c = new XYChart(600, 300, 0xeeeeee, 0x000000, 1);
$c->setRoundedFrame();
# Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to white (ffffff)
# and border and grid colors to grey (cccccc)
$c->setPlotArea(60, 60, 520, 200, 0xffffff, -1, 0xcccccc, 0xccccccc);
# Add a title to the chart using 15pt Times Bold Italic font, with a dark green (006600) background
# and with glass lighting effects.
$c->addTitle("Global Revenue for Year $selectedYear", "timesbi.ttf", 15, 0xffffff)->setBackground(
0x006600, 0x000000, perlchartdir::glassEffect($perlchartdir::ReducedGlare));
# Add a legend box at (70, 32) (top of the plotarea) with 9pt Arial Bold font
$c->addLegend(70, 32, 0, "arialbd.ttf", 9)->setBackground($perlchartdir::Transparent);
# Add a stacked area chart layer using the supplied data
my $layer = $c->addAreaLayer2($perlchartdir::Stack);
$layer->addDataSet($software, 0x40ff0000, "Software");
$layer->addDataSet($hardware, 0x4000ff00, "Hardware");
$layer->addDataSet($services, 0x40ffaa00, "Services");
# Set the area border color to the same as the fill color
$layer->setBorderColor($perlchartdir::SameAsMainColor);
# Set the x axis labels. In this example, the labels must be Jan - Dec.
my $labels = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec"];
$c->xAxis()->setLabels($labels);
# Set the y axis title
$c->yAxis()->setTitle("USD (Millions)");
# Set axes width to 2 pixels
$c->xAxis()->setWidth(2);
$c->yAxis()->setWidth(2);
# Output the chart in PNG format
binmode(STDOUT);
print "Content-type: image/png\n\n";
print $c->makeChart2($perlchartdir::PNG); |