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

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.

Table of Contents

FusionCharts XT in PHP Series

• Part 1 – JavaScript charts in PHP using FusionCharts XT
• Part 2 – JavaScript charts in PHP and MySQL using FusionCharts XT
• Part 3 – Drill-down JavaScript charts in PHP and MySQL
• Part 4 – JavaScript LinkedCharts in PHP and MySQL
• Part 5 – Using Debugging Tools for JavaScript Charts

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

• PHP running on any server
• MySQL
• The latest version of FusionCharts XT
• The sample World database provided by MySQL. Read more to understand the structure of this database and how to set it up.
• The source code for the 2nd part of the series

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.
include('Includes/DBConn.php');
include('Includes/FusionCharts.php');

// 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"
"https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

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:

• Links that open in the same tab
• Links that open in a new tab
• Links that open in a specified frame
• Links that open in a new pop-up window
• Links that invoke JavaScript functions
• Use the whole chart as a hotspot to use the simple link types named above

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.