In the 3rd part of FusionCharts XT in PHP series, we will create drill-down PHP charts using data from a MySQL database. Drill-down charts are immensely useful when you want to plot the subset of an aggregate data set. A generic use case can be world-wide sales data >> by country >> by state >> by city. FusionCharts XT supports unlimited levels of drill-down, and the chart types and chart attributes can be configured for each level. To create drill-down charts in PHP, we will use the PHP script that is bundled with the FusionCharts Suite. This script automatically generates the HTML, JavaScript and XML required to plot your charts, so that you don’t need to.

FusionCharts XT in PHP Series

What we are going to visualize

In the 2nd part, we created a chart showing the Top 10 Most Populous Countries in the World. We will add drill-down functionality to it, such that upon clicking on one of the countries, it will show us the Top 10 Most Populous Cities of that particular country.

What we will need

Our installation process will be the same as we did in the previous article. You may use the same setup or create it as follows:
  1. Within the root directory of your web server, create a folder named FusionCharts_XT_with_PHP. This will be our demo folder.
  2. Copy the entire Charts folder from the FusionCharts XT Download Package and paste it within our demo folder. This completes the installation of FusionCharts XT in our web application.
  3. Copy the Includes folder from FusionCharts XT Download Package > Code > PHP > Includes to our demo folder.
  4. Copy the source files used in the previous article and paste them in our demo folder

Attributes of a Drill-down chart

To create a drill-down chart, you will require the following 3 things:
  1. A parent chart (which we already have)
  2. The link attribute added to the dataplots of the parent chart (see below how to add this attribute)
  3. The child chart (we need to create this)
To create a dataplot (be it column, pie or line/area anchor) in XML, you would have to specify the label and the value attributes for the set element. Now to add drill-down to that dataplot, you will have to specify the link attribute. With the above XML, the dataplot, when clicked, will go to the page DrillDown_Child_Chart.php?country=China, which contains another chart to show some other data. In FusionChartsXT_with_PHP_and_MySQL_DataString.php, edit the part where you create the dataplots as below:
$strXML .= "";
Now that we have the first 2 things required for a drill-down chart, let us create the page that will show the child chart. This child chart needs to show a specific subset of the aggregate data; data related only to the parent dataplot. Create a blank PHP file named DrillDown_Child_Chart.php in our demo folder and write the following code in it:
< ?php
// Include the DBConn.php and FusionCharts.php files, so that we can access their variables and functions.

// Retrieve the name of the country from the $_REQUEST parameter
$countryName = $_REQUEST['country'];

// Use the connectToDB() function provided in DBConn.php, and establish the connection between PHP and the World database in our MySQL setup.
$link = connectToDB();

// Form the SQL query which will return the Top 10 Most Populous Cities according to the specified Country.
$strQuery = 'SELECT city.Name AS City, city.Population AS Population FROM city WHERE city.CountryCode = (SELECT country.Code FROM country WHERE country.Name = "'.$countryName.'") ORDER BY Population DESC LIMIT 10';

// Execute the query, or else return the error message.
$result = mysql_query($strQuery) or die(mysql_error());

// If we get a valid response - 
if ($result) {
	// Create the chart's XML string. We can add attributes here to customize our chart.
	$strXML = "";
	while($ors = mysql_fetch_array($result)) {
		// Append the names of the cities and their respective populations to the chart's XML string.
		$strXML .= "";
// Close the chart's XML string.
$strXML .= "";	
< !DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
< ?php // Set the rendering mode to JavaScript FC_SetRenderer(‘javascript’); // Call the renderChart method, which would return the HTML and JavaScript required to generate the chart echo renderChart(‘Charts/Column2D.swf’, // Path to chart type ”, // Empty string when using Data String method $strXML,// Variable which has the chart data ‘top10_most_populous_cities’, // Unique chart ID ‘660’, ‘400’, // Width and height in pixels false, // Disable debug mode true // Enable ‘Register with JavaScript’ (Recommended) ); ?>
Save this file. Navigate to parent chart first. On clicking one of the columns, it would load a new page with the child chart showing the relevant data.

Customizing the Drill-down chart

In FusionCharts XT, you can define the following types of links: You can read more about customizing drill-down charts in our documentation. Another good read is our previous blog post where we talk about making your drill-down charts more intuitive.

Download source files for these samples

Next in the series

In the next part of this series, we are going to tackle LinkedCharts. It is a first-of-its-type concept using which you can create unlimited levels of drill-down charts. You may read more about LinkedCharts in our documentation.

Take your data visualization to a whole new level

From column to donut and radar to gantt, FusionCharts provides with over 100+ interactive charts & 2,000+ data-driven maps to make your dashboards and reports more insightful

Explore FusionCharts

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 responses on “Drill-down JavaScript charts in PHP and MySQL – Part 3

  1. Kiểu dáng hiện đại. Mỗi ngăn kéo có 1 tay nắm nhựa.

  2. Искал информацию о том установить люстру в спальной комнате, нащел всю необходимую информацию в этой статье

  3. Simply wish to say your article is as astonishing.
    The clearness for your post is just nice and i can think you’re knowledgeable on this subject.
    Fine along with your permission allow me to grasp your feed to keep updated with impending post.
    Thank you 1,000,000 and please keep up the gratifying work.