|
|
Here, we'll see how to generate a combination
chart dynamically from a database.
We'll plotting a 3D Column Line Dual Y axis combination chart showing
the Top 5 customers for a particular year. On the primary axis, we'll
have the Amount figure representing the amount (in dollars) which has
been transacted with that customer. On the secondary axis, we'll have
Quantity axis representing the quantity traded for that year for that
particular customer. The end chart would look as under: |
|
As you can see above, we've extracted the amount
and quantity figure for the top 5 customers for a particular year. All this
data is contained in a view called Invoices.
The entire XML code required for the above chart is as under (say for the
year 1997): |
<graph PYAxisName='Amount' SYAxisName='Quantity'
showvalues='0' rotateNames='0' showAnchors='1' decimalPrecision='2' limitsDecimalPrecision='0'
divLineDecimalPrecision='0' hoverCapSepChar=' ' showShadow='0' lineThickness='3'>
<categories> <category name='QUICK-...' hoverText='QUICK-Stop'/>
<category name='Save-a...' hoverText='Save-a-lot Markets'/>
<category name='Ernst ...' hoverText='Ernst Handel'/>
<category name='Mère P...' hoverText='Mère
Paillarde'/> <category name='Hungry...' hoverText='Hungry
Owl All-Night Grocers'/> </categories> <dataset seriesname=''
showValue='1' color='AFD8F8' numberPrefix='$' > <set
value='61109.92' /> <set value='57713.58' />
<set value='48096.27' /> <set
value='23332.31' /> <set value='20454.4' />
</dataset> <dataset seriesname='' showValue='1' color='A66EDD'
parentYAxis='S' numberSuffix=' pcs.' anchorSides='12' anchorRadius='3' anchorBorderColor='A66EDD'>
<set value='2172' /> <set
value='2574' /> <set value='1950' />
<set value='750' /> <set
value='799' /> </dataset> </graph> |
In the above XML, we first define the basic
attributes (both functional and visual) of the <graph>
element. Thereafter, we render the <categories>
elements. Each <category> element
represents a category of data (in our example it's the top five customers
whose data we're comparing). We've shown only the first 5 characters of
the customer name, followed by ellipses, instead of the full customer
name to avoid cluttering on the chart. We're showing the full customer
name as tool tip. Post that, we render two <dataset>
blocks - one for primary axis representing Amount figure and the second
for the secondary axis representing the Quantity axis.
Let's now quickly code this chart in PHP using dataURL
method. It's always recommended to use the dataURL
method when working with combination charts too, as the XML data document
tends to get larger in size and as such, older versions of browsers are
not very able to handle long data chunks in the same page.
As we had earlier seen, the dataURL method
requires two pages:
- Chart Container Page - This page contains the chart object alongwith
any other HTML objects. We'll name this page as Chart.php
- in this page, we let the user select the year for which he wants to
view the chart and would show the chart.
- Data Provider Page - This page provides the XML data required by
the chart. This page is invoked by FusionCharts contained in Chart.php.
We'll name this page as Data.php.
The above two are present in the downloads under the folder FusionCharts2_3
> Sample Code > DBExamples > PHP> Combination.
We'll also need two other pages:
- Connection_ini.php- This page contains
the data connection details.
- FC_Colors.php - This page contains a
list of hex colors (without #) in an array named as strColor.
The above two include files are present under FusionCharts2_3
> Sample Code > DBExamples > PHP folder.
FC_Colors.php looks as under: |
<?php
$strColor[0] = 'AFD8F8';
$strColor[1] = 'F6BD0F';
$strColor[2] = '8BBA00';
$strColor[3] = 'A66EDD';
$strColor[4] = 'F984A1';
$strColor[5] = 'CCCC00'; #Chrome Yellow+Green
$strColor[6] = '999999'; #Grey
$strColor[7] = '0099CC'; #Blue Shade
$strColor[8] = 'FF0000'; #Bright Red
$strColor[9] = '006F00'; #Dark Green
$strColor[10] = '0099FF'; #Blue (Light)
$strColor[11] = 'FF66CC'; #Dark Pink
$strColor[12] = '669966'; #Dirty green
$strColor[13] = '7C7CB4'; #Violet shade of blue
$strColor[14] = 'FF9933'; #Orange
$strColor[15] = '9900FF'; #Violet
$strColor[16] = '99FFCC'; #Blue+Green Light
$strColor[17] = 'CCCCFF'; #Light violet
$strColor[18] = '669900'; #Shade of green
$strColor[19] = '1941A5'; #Dark Blue
?> |
Basically, this file is a list of hex colors
that we'll be using for the chart. In the above list, we've stored only
20 colors. You can extend it to any number. You should note that all the
above colors are without the #. We'll include this file in all our pages,
where we generate the XML data for FusionCharts. |
|
Connection_ini.php is the
page which contains the database connection details. When you run the
code on your machine, after downloading the package, you should ensure
that this page is updated so as to reflect the connection details to your
SQL server. This page looks as under: |
<?php
$dsn="Northwind";
$username="sa";
$password="sa";
?> |
|
The page Data.php is responsible
for relaying virtual XML documents to the FusionCharts embedded in Chart.php.
You should note that Data.php does NOT write
XML data documents to the server disk physically. It relays the XML data
document virtually (i.e., no XML files are created and saved on the server).
The code for this page can be reproduced as under: |
<?php
//Include connection and colors file
include '../Connection_ini.php';
include '../FC_Colors.php';
$sqlconnect=odbc_connect($dsn,$username,$password);
$strXMLData = "<graph PYAxisName='Amount' SYAxisName='Quantity'
showvalues='0' rotateNames='0' showAnchors='1' decimalPrecision='2' limitsDecimalPrecision='0'
divLineDecimalPrecision='0' hoverCapSepChar=' ' showShadow='0' lineThickness='3'>"
. chr(13);
$strCategories = "<categories>" . chr(13);
$strAmountDS = "<dataset seriesname='' showValue='1'
color='" . $strColor[0] . "' numberPrefix='$' >" . chr(13);
$strQuantityDS = "<dataset seriesname='' showValue='1'
color='" . $strColor[3] . "' parentYAxis='S' numberSuffix='
pcs.' anchorSides='12' anchorRadius='3' anchorBorderColor='" . $strColor[3]
. "'>" . chr(13);
$strCategories = "<categories>" .
chr(13);
$strDataSet = "";
$intCounter=1;
$strSQL="SELECT TOP 5 CustomerName, SUM(ExtendedPrice)
As Total, SUM(Quantity) As Quantity, SUM(Discount*ExtendedPrice) As Discount
FROM Invoices WHERE YEAR(OrderDate)=" . $_GET["year"] .
" GROUP BY CustomerName ORDER BY SUM(ExtendedPrice) DESC";
$oRs=odbc_exec($sqlconnect, $strSQL);
$intCounter=0;
while(odbc_fetch_row($oRs)) {
$strCategories = $strCategories .
"<category name='" . Substr(odbc_result($oRs,"CustomerName"),
0,5) . "...' hoverText='" . odbc_result($oRs,"CustomerName")
. "'/>" . chr(13);
$strAmountDS = $strAmountDS . "<set
value='" . odbc_result($oRs,"Total") . "' />"
. chr(13);
$strQuantityDS = $strQuantityDS .
"<set value='" . odbc_result($oRs,"Quantity") .
"' />" . chr(13);
$intCounter=$intCounter + 1;
}
$strCategories = $strCategories . "</categories>"
. chr(13);
$strAmountDS = $strAmountDS . "</dataset>"
. chr(13);
$strQuantityDS = $strQuantityDS . "</dataset>"
. chr(13);
$strXMLData = $strXMLData . $strCategories . $strAmountDS.
$strQuantityDS . "</graph>";
echo($strXMLData);
?> |
In the above code, we're doing the following:
- We first request the year, for which we have to show the data. The
year was passed in the format Data.php?year=1996
(or 1997 or 1998) by the FusionCharts charts contained in the page Chart.php
- Next, we initialize a few string variables which would be used to
contain the XML data. strXMLData will
be used to contain the entire XML data document for the chart. strCategories
will be used to store the <categories>
element and it's child <category>
elements. strAmountDs will be used to
store the amount <dataset> and <set>
elements. strQuantityDs will be used to
store the quantity <dataset> and
<set> elements.
- We now initialize the <graph> element
in the variable strXMLData and opening
<categories> tag in the variable
strCategories.
- We also initialize the opening <dataset>
tags for both dataset blocks in the variables strAmountDS
and strQuantityDS. The first dataset has been set with a parentYAxis
attribute as P (primary) and the second one with S (secondary).
- Next, we retrieve the recordset from the database for top 5 customers
for the selected year and iterate through the records one by one. For
each record, we add a <category> element
to the variable strCategories, and <set>
elements in the format <set value='xxx' />
to the variables strAmountDS
and strQuantityDS.
- Finally, we close all the open elements in the XML data document (like
<categories>, <dataset> and
<graph>) using simple string concatenation
and form a full XML doc in strXMLData.
- In the end, we just write this XML data document to the output stream.
When you try viewing this page individually in the browser (in the format
http://localhost/...Path.../DBExamples/PHP/Combination/Data.php?year=1997)
you'll get the XML output which we had earlier seen. You should
always make sure, that while using dataURL
method, your dataURL script should output
just XML data and no HTML tags.
Let's now shift our attention to Chart.php
and see the code it contains to embed the chart.
Chart.php contains the following code: |
<HTML>
<HEAD>
<TITLE>FusionCharts</TITLE>
<LINK REL='Stylesheet' HREF='../Style.css'>
</HEAD>
<?php
include '../Connection_ini.php';
include '../FC_Colors.php';
$sqlconnect=odbc_connect($dsn,$username,$password);
$strSQL="SELECT DISTINCT YEAR(OrderDate) As Year
FROM Orders ORDER BY 1";
$oRsYears=odbc_exec($sqlconnect, $strSQL);
if ($_POST) {
$intYear = $_POST['year'];
}
else {
while(odbc_fetch_row($oRsYears)){
$intYear = odbc_result($oRsYears,"Year");
}
}
$strDataURL = "Data.php?year=" . $intYear;
$strDataURL = urlencode($strDataURL);
?>
<table width="500" border="0" cellpadding="2"
cellspacing="0" class="tableWithBorder" align='center'>
<tr>
<td colspan="3" class="trdark"><div align="center"><span
class="textboldlight">FusionCharts</span></div></td>
</tr>
<form action="Chart.php" method="post">
<tr>
<td colspan="3" class="text" align="center">Please
select the year for which you want to see the chart:
<SELECT name='year' class='select' onChange="this.form.submit();">
<?
$oRsYears=odbc_exec($sqlconnect, $strSQL);
while(odbc_fetch_row($oRsYears)){
echo("<option value='"
. odbc_result($oRsYears,"Year") . "'");
if (odbc_result($oRsYears,"Year")==$intYear){
echo(" selected");
}
echo(" >" . odbc_result($oRsYears,"Year"));
}
?>
</SELECT>
</td>
</tr>
</form>
<tr>
<td colspan="3"> </td>
</tr>
<tr>
<td valign="top">
<table width="98%" border="0" cellspacing="0"
cellpadding="2" align='center'>
<tr>
<td>
<div align="center" class="text">
<OBJECT classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000"
codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,0,0"
WIDTH="500" HEIGHT="300" id="FusionCharts"
ALIGN="">
<PARAM NAME="FlashVars" value="&dataURL=<?=$strDataURL?>">
<PARAM NAME=movie VALUE="../../../Charts/FC_2_3_MSColumnLine_DY_3D.swf?chartWidth=500&chartHeight=300">
<PARAM NAME=quality VALUE=high>
<PARAM NAME=bgcolor VALUE=#FFFFFF>
<EMBED src="../../../Charts/FC_2_3_MSColumnLine_DY_3D.swf?chartWidth=500&chartHeight=300"
FlashVars="&dataURL=<?=$strDataURL?>"
quality=high bgcolor=#FFFFFF WIDTH="500" HEIGHT="300"
NAME="FusionCharts" ALIGN="" TYPE="application/x-shockwave-flash"
PLUGINSPAGE="http://www.macromedia.com/go/getflashplayer"></EMBED>
</OBJECT>
</div>
</td>
</tr>
</table>
</td>
</tr>
</table>
<p align="center" class="text">©All
Rights Reserved - InfoSoft Global Private Limited - 2005 - <a href="http://www.InfoSoftGlobal.com"
target="_blank">www.InfoSoftGlobal.com</a></p>
</BODY></html> |
Here, in the above code, we're doing the
following:
- First, we're requesting a list of unique years from the database and
rendering them in a drop down box. By default, we choose the last year
as the selected year (i.e., the year for which we'll show the chart)
and store it in the variable intYear.
When the user selects a different year from the drop down box, the forms
submits to this page only and the new requested year is stored in the
variable intYear.
- Next, we generate the dataURL string in the format Data.php?year=(intYear)
(i.e., Data.php?year=1996)
- Thereafter, we embed the FusionCharts chart in this page and provide
the above dataURL to it (see the code
in bold above).
When you now view Chart.php, you'll get
the following chart: |
|
|