Using FusionCharts XT with PHP - Plotting data from a database

You are viewing documentation for an older version. For current documentation - click here.

Plotting data from a database

In this section, we will show you how to use FusionCharts XT and PHP to plot charts from data contained in a database. Here, we will do the following:

Create a pie chart to show "Production by Factory" by initially using the Data String method
Convert the chart to use the Data URL method
Create a multi-series line chart from database

We have used MySQL database here. The database dump is present in Download Package > Code > PHP > DB folder. You can, however, use any database with FusionCharts XT including MS SQL, Oracle, Access etc.

Before you go further with this page, we recommend you to please see the previous section, Basic Examples, as we start off from concepts explained in that page.

The code examples contained in this page are present in Download Package > Code > PHP > DBExample folder. The MySQL database dump is present in Download Package > Code > PHP > DB.

Before we code the PHP pages to retrieve data, let's quickly have a look at the database structure.

The database contains just two tables:

Factory_Master: To store the name and id of each factory
Factory_Output: To store the number of units produced by each factory for a given date.

For demonstration, we have fed some dummy data in the database. Let's now shift our attention to the PHP page that will interact with the database, fetch data and then render a chart.

The PHP page for the Data String method example is named as BasicDBExample.php (in DBExample folder). It contains the following code:

 <?php
      //We have included ../Includes/FusionCharts.php and ../Includes/DBConn.php, which contains
      //functions to help us easily embed the charts and connect to a database.
      include("../Includes/FusionCharts.php");
      include("../Includes/DBConn.php");
      ?>
      <HTML>
         <HEAD>
        <TITLE>FusionCharts XT - Database Example</TITLE>
        <SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
     </HEAD>
     <BODY>
     <CENTER>
     <?php   
   //In this example, we show how to connect FusionCharts to a database.
   //For the sake of ease, we have used a MySQL database containing two
   //tables.
   //Connect to the DB
   $link = connectToDB();
   //$strXML will be used to store the entire XML document generated
   //Generate the chart element
     $strXML = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'>";
   //Fetch all factory records
     $strQuery = "select * from Factory_Master";
     $result = mysql_query($strQuery) or die(mysql_error());
   //Iterate through each factory
       if ($result) {
          while($ors = mysql_fetch_array($result)) {
          //Now create a second query to get details for this factory
           $strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" . $ors['FactoryId'];
           $result2 = mysql_query($strQuery) or die(mysql_error()); 
           $ors2 = mysql_fetch_array($result2); 
          //Generate <set label='..' value='..'/>
           $strXML .= "<set label='" . $ors['FactoryName'] . "' value='" . $ors2['TotOutput'] . "' />";
          //free the resultset
           mysql_free_result($result2);
          }
     }
           mysql_close($link);
           //Finally, close <chart> element
           $strXML .= "</chart>";
           //Create the chart - Pie 3D Chart with data from $strXML
           echo renderChart("../../FusionCharts/Pie3D.swf", "", $strXML, "FactorySum", 600, 300, false, true);
        ?>
  </BODY>
</HTML>

The following actions are taking place in this code:

Including the FusionCharts.js JavaScript class and FusionCharts.php , to enable easy embedding of FusionCharts XT.
Including DBConn.php, which contains connection parameters to connect to MySQL database.
Generating the XML data document by iterating through each record and store it in strXML variable.
Rendering the chart using renderChart() method and pass strXML as dataStr.

When you now run the code, the following chart appears:

Let's now convert this example to use the Data URL method. As previously explained, in the Data URL mode, you need two pages:

Chart Container Page - The page which embeds the HTML code to render the chart. This page also tells the chart where to load the data from. We will name this page as Default.php.
Data Provider Page - This page provides the XML data to the chart. We will name this page as PieData.php

The pages in this example are contained in Download Package > Code > PHP > DB_dataURL folder.

Chart Container Page - Default.php

Default.php contains the following code to render the chart:

<?php
      //We have included ../Includes/FusionCharts.php, which contains functions
      //to help us easily embed the charts.
      include("../Includes/FusionCharts.php");
      ?>
      <HTML>
      <HEAD>
   <TITLE>	FusionCharts XT  - Data URL and Database  Example</TITLE>
   <SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
</HEAD>
<BODY>
   <?php
   //In this example, we show how to connect FusionCharts to a database 
   //using the  Data URL method. In our previous example, we have used Data String method
   //where the XML is generated in the same page as chart. Here, the XML data
   //for the chart will be generated in PieData.php.
   //To illustrate how to pass additional data as querystring to dataURL, 
   //we have added an animate	property, which will be passed to PieData.php. 
   //PieData.php will handle this animate property and then generate the 
   //XML accordingly.
   //For the sake of ease, we have used MySQL databases containing two
   //tables.
   //Set DataURL with animation property to 1
   //NOTE: It's necessary to encode the dataURL if you've added parameters to it
   $strDataURL = encodeDataURL("PieData.php?animate=1");
   //Create the chart - Pie 3D Chart with dataURL as strDataURL
   echo renderChart("../../FusionCharts/Pie3D.swf", $strDataURL, "", "FactorySum", 600, 300, false, true);
?>
</BODY>
</HTML>

In the above code, we are:

Including FusionCharts.js JavaScript class and FusionCharts.php
Create the dataURL string and store it in strDataURL variable. We append a dummy property- animate to show how to pass parameters to dataURL. After building the dataURL, we encode it using encodeDataURL function defined in FusionCharts.php.
Finally, we are rendering the chart using the renderChart() method and set dataURL as strDataURL.

Creating the data provider page PieData.php

PieData.php contains the following code to output XML Data:

 <?php
   //We have included  ../Includes/DBConn.php, which contains functions 
   //to help us easily connect to a database.
   include("../Includes/DBConn.php");
   //This page generates the XML data for the Pie Chart contained in
   //Default.php.
   //For the sake of ease, we have used a MySQL databases containing two
   //tables.
   //Connect to the DB
     $link = connectToDB();
   //Default.php has passed us a property animate. We request that.
     $animateChart = $_GET['animate'];
   //Set default value of 1
     if ($animateChart=="")
        $animateChart = "1";
   //$strXML will be used to store the entire XML document generated
   //Generate the chart element
      $strXML = "<chart caption='Factory Output report' subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation=' " . $animateChart . "'>";
   //Fetch all factory records
      $strQuery = "select * from Factory_Master";
      $result = mysql_query($strQuery) or die(mysql_error());
    //Iterate through each factory
      if ($result) {
           while($ors = mysql_fetch_array($result)) {
           //Now create a second query to get details for this factory
           $strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" . $ors['FactoryId']
           $result2 = mysql_query($strQuery) or die(mysql_error());
           $ors2 = mysql_fetch_array($result2);
           //Generate <set label='..' value='..' />
           $strXML .= "<set label='" . $ors['FactoryName'] . "' value='" . $ors2['TotOutput'] . "' />";
           //free the resultset
           mysql_free_result($result2);
        }
     }
           mysql_close($link);
           //Finally, close <chart> element
           $strXML .= "</chart>";
           //Set Proper output content-type
           header('Content-type: text/xml');
           //Just write out the XML data
           //NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER
           echo $strXML;
        ?>

In the above page:

We first request the animate property which has been passed to it (from dataURL)
We generate the data and store it in the strXML variable
Finally, we write this data to output stream without any HTML tags.

When you view this page, you will get the same output as before.

Let's now see how we can create a multi-series chart from database. This is no different from creating the Pie chart we saw above.

All you need to do is to build proper XML from the database that you query from your database. Multi-series charts have a different data format from the format of an XML for a single-series chart like pie. Let us see how the code will look for this:

<?php
  //In this example, we show how to connect FusionCharts to a database.
  //For the sake of ease, we have used an MySQL databases containing two
  //tables.
  // Connect to the DB
  $link = connectToDB();
  // SQL query for category labels
  $strQueryCategories = "select distinct DATE_FORMAT(factory_output.DatePro,'%c-%d-%Y') as DatePro from factory_output order by DatePro";	
  // Query database
  $resultCategories = mysql_query($strQueryCategories) or die(mysql_error());
  // SQL query for factory output data
  $strQueryData = "select factory_master.FactoryName, DATE_FORMAT(factory_output.DatePro,'%c-%d-%Y') as DatePro, factory_output.Quantity from factory_master factory_master, factory_output factory_output where factory_output.FactoryID = factory_master.FactoryId order by factory_output.FactoryID, factory_output.DatePro";	
  // Query database
  $resultData = mysql_query($strQueryData) or die(mysql_error());
  //We also keep a flag to specify whether we have to animate the chart or not.
  //If the user is viewing the detailed chart and comes back to this page, he shouldn't
  //see the animation again.
  //$strXML will be used to store the entire XML document generated
  //Generate the chart element
  $strXML = "<chart legendPosition='' caption='Factory Output report' subCaption='By Quantity' xAxisName='Factory' yAxisName='Units' showValues='0' formatNumberScale='0' rotateValues='1' animation='1'>";
  // Build category XML
  $strXML .= buildCategories ($resultCategories, "DatePro");
  // Build datasets XML
  $strXML .= buildDatasets ( $resultData, "Quantity", "FactoryName");
  //Finally, close <chart> element
  $strXML .= "</chart>";
  //Create the chart - Pie 3D Chart with data from strXML
  echo renderChart("../../FusionCharts/MSLine.swf", "", $strXML, "FactorySum", 700, 400, false, true);
  // Free database resource
  mysql_free_result($resultCategories);
  mysql_free_result($resultData);
  mysql_close($link);

/***********************************************************************************************
* Function to build XML for categories
* @param	$result         Database resource
* @param 	$labelField 	Field name as String that contains value for chart category labels
* 
*	@return categories XML node 
*/
function buildCategories ( $result, $labelField )
{
  $strXML = "";
  if ($result) {
    $strXML = "<categories>";
    while($ors = mysql_fetch_array($result)) {
      $strXML .= "<category label='" . $ors[$labelField]. "'/>";
    }
    $strXML .= "</categories>";
  }
  return $strXML;
}

/***********************************************************************************************
* Function to build XML for datasets that will contain chart data
* @param	$result 	Database resource. The data should come ordered by a control break 
field which will require to identify datasets and set its value to 
dataset's series name
* @param 	$valueField 	Field name as String that contains value for chart dataplots
* @param 	$controlBreak 	Field name as String that contains value for chart dataplots
* 
*	@return Dataset XML node 
*/
function buildDatasets ($result, $valueField, $controlBreak )
{
  $strXML = "";
  if ($result) {
    $controlBreakValue ="";
    while( $ors = mysql_fetch_array($result) ) {
      if( $controlBreakValue != $ors[$controlBreak] ) 
      {
        $controlBreakValue =  $ors[$controlBreak];
        $strXML .= ( $strXML =="" ? "" : "</dataset>") . ( "<dataset seriesName='" . $controlBreakValue . "'>" ) ;
      }
      $strXML .= "<set value='" . $ors[$valueField] . "'/>";
    }
    $strXML .= "</dataset>";
  }
  return $strXML;
}
?>

The following actions are taking place in this code:

We first include the FusionCharts.js JavaScript class and FusionCharts.php and DBConn.php
We query database for all distinct dates to add them as categories labels of the chart. This is handled by the buildCategories function
We query database for all factory-wise and dates-wise output data to add them to chart (using the buildDatasets function) data where data for each factory becomes a dataset
Finally, we render the chart using the renderChart() method and pass strXML as dataStr.

The chart will look as shown in the image below: