Using FusionCharts PHP Class > Creating Drill-down charts

In our previous example, we had used FusionCharts to plot a chart using data stored in database. We'll now extend that example itself to create a drill-down chart which can show more information.

Before you go further with this page, we recommend you to please see the previous sections like "Basic Examples", Creating Data from Array" as we start off from concepts explained in those pages.

If you recall from previous example, we were showing the sum of factory output in a Pie chart as under:

In this example, we'll extend this example, so that when a user clicks on a Pie slice for a factory, he can drill down to see date wise production for that factory.
 
Setting up the pie chart for Link
To set up the pie chart to enable links for drill-down involves just minor tweaking of our previous BasicDBExample.php. We basically need to add the link attribute for each <set> element. We create a new page Default.php (in DB_DrillDown folder) from the previous page with the following code changes:

The code examples contained in this page are contained in Download Package > Code > PHPClass > DB_DrillDown folder.

<?php
  //We've included ../Includes/FusionCharts_Gen.php, which contains
  //FusionCharts PHP Class to help us easily embed charts
  //We've also used ../Includes/DBConn.php to easily connect to a database
  include("../Includes/FusionCharts_Gen.php");
  include("../Includes/DBConn.php");
?>
<HTML>
<HEAD>
  <TITLE>
    FusionCharts Free - Database and Drill-Down Example
</TITLE>

<?php
  //You need to include the following JS file, if you intend to embed the chart using JavaScript.
  //Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
  //When you make your own charts, make sure that the path to this JS file is correct. Else, you   //would get JavaScript errors.
?>
  <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've used an MySQL databases containing two
  //tables.

  # Connect to the Database
  $link = connectToDB();

  # Create a pie 3d chart object
  $FC = new FusionCharts("Pie3D","650","450");

  # Set Relative Path of chart swf file.
  $FC->setSwfPath("../../FusionCharts/");

  #Store chart attributes in a variable
  $strParam="caption=Factory Output report;subCaption=By Quantity;pieSliceDepth=30;showBorder=1; showNames=1;formatNumberScale=0;numberSuffix= Units";

  # Set chart attributes
  $FC->setChartParams($strParam);

  # Fetch all factory records creating SQL query
  $strQuery = "select a.FactoryID, b.FactoryName, sum(a.Quantity) as total from Factory_output a, Factory_Master b where a.FactoryId=b.FactoryId group by a.FactoryId,b.FactoryName";

  $result = mysql_query($strQuery) or die(mysql_error());

  #Pass the SQL query result and Drill-Down link format to PHP Class Function
  # this function will automatically add chart data from database
  /*
   The last parameter passed i.e. "Detailed.php?FactoryId=##FactoryID##"
   drill down link from the current chart   
   Here, the link redirects to another PHP file Detailed.php
   with a query string variable -FactoryId
   whose value would be taken from the Query result created above.
   Any thing placed between ## and ## will be regarded
   as a field/column name in the SQL query result.
   Value from that column will be dynamically assingned as the query variable's value
   Hence, for each dataplot in the chart the resultant query variable's value
   will be different
  */

  if ($result)
  {
    $FC->addDataFromDatabase($result, "total", "FactoryName","","Detailed.php?FactoryId=##FactoryID##");
  }

  mysql_close($link);

  #Create the chart
  $FC->renderChart();
?>

</CENTER>
</BODY>
</HTML>
 
As you can see in the code above, we're doing the following:
  1. Include FusionCharts.js JavaScript class, FusionCharts.php and DbConn.php to enable easy embedding of FusionCharts. DBConn.php contains connection parameters to connect to MySQL database.
  2. Call connectToDB() function to set up database connection
  3. Create FusionCharts PHP class object for Pie 3D chart
  4. Set relative path of chart SWF file
  5. Store chart attributes in a variable $strParam
  6. Set chart attributes using setChartParams() function
  7. Fetch required records from database and store the output in $result
  8. Call addDataFromDatabase() function to add chart data from database; we specify the link attribute which points to Detailed.php page that contains the chart to show details.The last parameter passed i.e. "Detailed.php?FactoryId=##FactoryID##" drill down link from the current chart. Here, the link redirects to another PHP file Detailed.php with a query string variable -FactoryId whose value would be taken from the Query result created above. Any thing placed between ## and ## will be regarded as a field/column name in the SQL query result. Value from that column will be dynamically assingned as the query variable's value. Hence, for each dataplot in the chart the resultant query variable's value will be different.
  9. Close database link
  10. Render chart
Please go through FusionCharts PHP Class API Reference section to know more about the functions used in the above code.
 
Let's now shift our attention to Detailed.php page.
 
Creating the detailed data chart page
The page Detailed.php contains the following code:

<?php
  //We've included ../Includes/FusionCharts_Gen.php, which contains
  //FusionCharts PHP Class to help us easily embed charts
  //We've also used ../Includes/DBConn.php to easily connect to a database
  include("../Includes/FusionCharts_Gen.php");
  include("../Includes/DBConn.php");
?>
<HTML>
<HEAD>
  <TITLE>
    FusionCharts Free - Database and Drill-Down Example
  </TITLE>

<?php
  //You need to include the following JS file, if you intend to embed the chart using JavaScript.
  //Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
  //When you make your own charts, make sure that the path to this JS file is correct.
  //Else, you would get JavaScript errors.
?>
  <SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>

</HEAD>
<BODY>
<CENTER>

<?php
  //This page is invoked from Default.php. When the user clicks on a pie
  //slice in Default.php, the factory Id is passed to this page. We need
  //to get that factory id, get information from database and then show
  //a detailed chart.

  //Request the factory Id from Querystring, we sent the FactoryId from Default.php
  $FactoryId = $_REQUEST['FactoryId'];

  # Create object of FusionCharts PHP class
  $FC = new FusionCharts("Column2D","600","300");

  # Set Relative Path of chart swf file.
  $FC->setSwfPath("../../FusionCharts/");

  // Store Chart attributes in a variable
  $strParam="caption=Factory " . $FactoryId . " Output;subcaption=(In Units);xAxisName=Date; formatNumberScale=0;decimalPrecision=0";

  # Set chart attributes
  $FC->setChartParams($strParam);

  // Connect to the DataBase
  $link = connectToDB();

  //Now, we get the data for that factory
  $strQuery = "select Quantity, DATE_FORMAT(DatePro,'%c/%e') as DDate from Factory_Output where FactoryId=" . $FactoryId;

  $result = mysql_query($strQuery) or die(mysql_error());

  //Pass the SQL query result to the FusionCharts PHP Class' function
  //that will extract data from databasa and add to the chart.
  if ($result)
  {
    $FC->addDataFromDatabase($result, "Quantity", "DDate");
  }

  mysql_close($link);

  //Create the chart - Column2D using FusionCharts PHP Class
  $FC->renderChart();
?>

</CENTER>
</BODY>
</HTML>
Let's analyze the steps involved in the above code:
  1. Including FusionCharts.js JavaScript class, FusionCharts.php and DBConn.php to enable easy embedding of FusionCharts.
  2. Requesting the factory id for which we've to show detailed data. This data was sent to us as query string, as a part of pie chart link. FactoryId is stored in a variable $FactoryId
  3. Creating FusionCharts PHP class object for Column 2D chart
  4. Setting relative path of chart SWF file
  5. Storing chart attributes in a variable $strParam
  6. Setting chart attributes through setChartParams() function
  7. Connecting to MySQL database through connectToDB() function
  8. Fetching required data from database and storing in a variable $result
  9. Passing $result to addDataFromDatabase() function that adds chart data from database
  10. Closing database connection
  11. Rendering chart
Please go through FusionCharts PHP Class API Reference section to know more about the functions used in the above code.
 
When you now run the app, you'll see the detailed page as under: