Creating Drill-down charts |
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. |
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 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:
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. |
Creating the detailed data chart 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:
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:
|