In our previous example, we had used FusionCharts XT to plot a chart using data stored in database. We will 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, Basic Examples and 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 will extend this example, so that when the users click a Pie slice for a factory, they can drill down to see date wise production for that factory.
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 have included ../Includes/FusionCharts_Gen.php, which contains
//FusionCharts PHP Class to help us easily embed charts
//We have also used ../Includes/DBConn.php to easily connect to a database
include("../Includes/FusionCharts_Gen.php");
include("../Includes/DBConn.php");
?>
<HTML>
<HEAD>
<TITLE>
FusionCharts XT - 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 will 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 have 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
$strParam="caption=Factory Output report;subCaption=By Quantity;pieSliceDepth=30;showBorder=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, that is, "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 will be taken from the Query result created above.
Anything placed between ## and ## will be regarded
as a field/column name in the SQL query result.
Value from that column will be dynamically assigned 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 are doing the following:
- Including the FusionCharts.js JavaScript class, and DbConn.php to enable easy embedding of FusionCharts. DBConn.php contains connection parameters to connect to database.
- Creating FusionCharts PHP class object for Pie 3D chart.
- Setting the relative path of chart SWF file.
- Storing chart attributes in a variable $strParam.
- Setting the chart attributes using setChartParams() function.
- Fetching required records from database and store the output in $result.
- Calling the 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, that is, "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 will be taken from the Query result created above. Anything placed between ## and ## will be regarded as a field/column name in the SQL query result. Value from that column will be dynamically assigned as the query variable's value. Hence, for each dataplot in the chart the resultant query variable's value will be different.
- Closing database link.
- Rendering chart .
Please go through the 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.
The page Detailed.php contains the following code:
<?php
//We have included ../Includes/FusionCharts_Gen.php, which contains
//FusionCharts PHP Class to help us easily embed charts
//We have also used ../Includes/DBConn.php to easily connect to a database
include("../Includes/FusionCharts_Gen.php");
include("../Includes/DBConn.php");
?>
<HTML>
<HEAD>
<TITLE>
FusionCharts XT - 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 will 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
$FactoryId = $_REQUEST['FactoryId'];
# Create a column 2D chart object
$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;decimals=0;rotateLabels=1;slantLabels=1";
# Set chart attributes
$FC->setChartParams($strParam);
// Connect to the DataBase
$link = connectToDB();
//Now, we get the data for that factory
//storing chart values in 'Quantity' column and category names in 'DDate'
$strQuery = "select Quantity, DATE_FORMAT(DatePro,'%m/%d/%Y') 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 database 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:
- Including FusionCharts.js JavaScript class, and DBConn.php to enable easy embedding of FusionCharts XT.
- Requesting the factory ID for which we have 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.
- Creating a FusionCharts PHP class object for Column 2D chart.
- Setting the relative path of chart SWF file.
- Storing the chart attributes in a variable $strParam.
- Setting the chart attributes through setChartParams() function.
- Connecting to MySQL database through the connectToDB() function.
- Fetching required data from database and storing in a variable $result.
- Passing $result to the addDataFromDatabase() function that adds chart data from database.
- Closing database connection.
- 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:
