FusionCharts and PHP > Using dataXML method
 

Previously, we had seen how to use the dataURL method to create a dynamic column chart using FusionCharts. Here, we'll be creating the same chart but using dataXML method.

As we had earlier seen, the dataXML method just requires one page:

  • Chart.php: This page contains everything - the year select drop down list, the chart and the XML data.

The above page is present in the downloads under the folder FusionCharts2_3 > Sample Code > DBExamples > PHP > dataXML.

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 Chart.php is responsible for everything - rendering the year down box, getting the selected year, creating the XML data for that year and then rendering the chart for that year. The code for this page can be reproduced as under:
<HTML>
<HEAD>
<TITLE>FusionCharts - dataXML Example</TITLE>
<LINK REL='Stylesheet' HREF='../Style.css'>
</HEAD>
<?php
   //Include connection and colors file
   include '../Connection_ini.php';
   include '../FC_Colors.php';

   //Connect to the ODBC Source
   $sqlconnect=odbc_connect($dsn,$username,$password);

   //Generate the query to get a list of unique years
   $strSQL="SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1";

   //Get the list of years
   $oRsYears=odbc_exec($sqlconnect, $strSQL);

   //If an year has been selected
   if ($_POST) {
      $intYear = $_POST['year'];
   }
   else {
      //By default select the last year.
      while(odbc_fetch_row($oRsYears)){
         $intYear = odbc_result($oRsYears,"Year");
      }
   }
   //Now, we'll start creating the XML data document.
   //--------------------------------------------------------------//
   //Generate the query to get the data
   $strSQL="SELECT TOP 5 Country, SUM(ExtendedPrice) As Total, COUNT(DISTINCT OrderID) As orderNumber FROM Invoices WHERE YEAR(OrderDate)=" . $intYear . " GROUP BY Country ORDER BY SUM(ExtendedPrice) DESC";

   //Create the <graph> element
   $strXMLData = "<graph caption='Top 5 Countries for the year " . $intYear . "' shownames='1' showvalues='0' decimalPrecision='0' numberPrefix='$'>" . chr(13);

   //Get the data
   $oRs=odbc_exec($sqlconnect, $strSQL);

   //Now iterate through each data row
   $intCounter=0;
   while(odbc_fetch_row($oRs)){
      //Append the value in format <set name='...' value='...' color='...' />
      $strXMLData = $strXMLData . "<set name='" . odbc_result($oRs,"Country") . "' value='" . odbc_result($oRs,"Total") . "' color='" . $strColor[$intCounter] . "'/>" . chr(13);
      //Increase the count
      $intCounter=$intCounter+1;
}
   //Entire XML - concatenation
   $strXMLData = $strXMLData . "</graph>";
?>
<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();">
<?
   //Get back to start position
   $strSQL="SELECT DISTINCT YEAR(OrderDate) As Year FROM Orders ORDER BY 1";
   $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">&nbsp;</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="450" HEIGHT="300" id="FusionCharts" ALIGN="">
<PARAM NAME="FlashVars" value="&dataXML=<?=$strXMLData?>">
<PARAM NAME=movie VALUE="../../../Charts/FC_2_3_Column3D.swf?chartWidth=450&chartHeight=300">
<PARAM NAME=quality VALUE=high>
<PARAM NAME=bgcolor VALUE=#FFFFFF>
<EMBED src="../../../Charts/FC_2_3_Column3D.swf?chartWidth=450&chartHeight=300" FlashVars="&dataXML=<?=$strXMLData?>" quality=high bgcolor=#FFFFFF WIDTH="450" 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">&copy;All Rights Reserved - InfoSoft Global Private Limited - 2005 - <a href="http://www.InfoSoftGlobal.com" target="_blank">www.InfoSoftGlobal.com</a></p>
</BODY></html>

In the above code, we're doing the following:

  1. 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.
  2. We initialize a variable strXMLData which would store the entire XML data document. We initialize the <graph> element in this string variable.
  3. Next, we retrieve the recordset from the database and iterate through the records one by one. For each record, we add a <set> element in the format <set name='DataName' value='DataValue' color='HexCode' />. We get individual color codes for each data item from the list of colors that we had stored in the array strColor (stored in FC_Colors.php).
  4. Thereafter, we add the ending </graph> element to the XML document (stored as string in strXMLData).
  5. Lastly, we embed the FusionCharts Chart in the PHP page and convey the XML data using dataXML method.

When you now see Chart.php in your browser, you'll get the following output: