1. Code
  2. PHP

How to Create Drill Down Charts Using PHP, MySQL and FusionCharts


Suppose you are viewing a column chart which displays yearly sales figures of your company, where each column represents one single year. If you click on a column, you go one level deep to see quarterly data. Now if you click once more, you arrive at monthly sales figures.

What you are doing here is drilling down from yearly to quarterly to monthly data using on a drill down chart. Drill-down charts allow you to click on individual data plots to reveal more details about it (a data plot refers to a column in column chart, lines in a line chart or pie slices in a pie chart).

Drill down charts are very useful in business reports and analytics dashboards. And in this tutorial, I am going to cover the step-by-step process of creating a drill down chart using FusionCharts’ JavaScript chart library. We are going to fetch the data present in a MySQL database using PHP, and then use FusionCharts’ core library and its PHP charts wrapper to plot our chart.

Here’s what we are making today: (you can see live version here or download the source code using this link).

It is a 2D column chart with two level drill-down. First level contains yearly sales data for 6 years. If you click on any year, you will be able to view quarterly data for that particular year (second level).

I have divided the complete process into seven steps:

  • 1: Including JavaScript files and PHP wrapper
  • 2: Initiating & validating DB connection
  • 3: Fetching data using SQL query
  • 4: Converting the query result to JSON
  • 5: Repeating steps -3 & 4 for each drill-down level
  • 6: Creating chart instance and closing DB connection

1. Including JavaScript Files and PHP Wrapper

To render charts in this project, we need both FusionCharts’ core JS library and its PHP wrapper:

Here is how our PHP and HTML code will look like after this step:

2. Initiating & Validating DB Connection

Once we have included all the dependencies, we need to initiate the connection with our database to fetch data for the chart.

Here is how we initiate and validate database connection in PHP:

To establish the connection with MySQL database these four parameters should be specified:

  • Address of the server where database is installed. In our case it islocalhost.
  • Username to connect to the database. Default value is root.
  • Password for that username.
  • Name of the database under which the table is created.

$dbhandle establishes connection with the database. I have included dummy values in above code snippet, and you will have to replace values for $hostdb, $userdb, $passdb, and $namedb with actual values for your database.

3. Fetching Data Using SQL Query

After the connection with MySQL database is successfully established, we can use below SQL query to fetch the data for our chart:

If query ($strQuery) is executed successfully, data will be populated in $result.

4. Converting the Query Result to JSON

If you followed all the above steps properly till now, you should have fetched data from query in $result variable.

To form the JSON data in correct format, we will create an associative array - $arrData - and push all the chart configuration attributes and data in it as shown in the code below:

This code contains the JSON data for the parent chart. The link attribute in JSON data refers to the next chart to be rendered, after clicking individual data plot. For a more detailed description, you can visit this official documentation page.

5. Repeating Steps-3 & 4 for Each Drill Down Level

To drill down to the next level, we have to fetch data again using the variable $resultQuarterly from the respective database table (quarterly_sales). This is similar to what we did in step 3. Code for the same is shown below:

The associative array $arrData is appended with the child-chart data for each parent-data plot, but here the data comes under linkeddata . The code for this is shown below with related comments:

Now , we have our associative array ready with data in the variable $arrData and finally we encode the array into our JSON format using json_encode() method. The variable $jsonEncodedData holds the data for the chart having drill down feature.

6. Creating Chart Instance and Closing DB Connection

An HTML <div> is best suited as a container for our chart and here is how we declare it:

As the next step, we will create chart instance and pass the chart type, its dimensions, container id and other details to it to render the chart. Here is the template for creating FusionCharts instance:

Here is the code to create FusionCharts instance (using above template), render the chart and finally close the database connection:

Making Your Charts Look Better

If you downloaded the source code for this project, then you must have noticed some parameters under chart array that we didn’t discuss above. Those parameters control looks and functionality of a chart and are formally referred to as chart attributes.

Here is some explanation on few key attributes I have used in my code:

  • canvasBgColor and bgColor: you can use these two attributes for controlling the background color of your chart’s canvas and its container.
  • baseFont: you can use this attribute to set font family for your chart. You are not restricted to only system fonts, but are free to add any font. To use a custom font, include the source file for that font in HTML and set it using baseFont attribute. Our example uses Open Sans from Google Fonts.
  • plotToolText: displaying additional information on hover is a great way to enhance data viz experience and this is where plotToolText comes into picture. It allows you to customize the tooltip for the chart. You can use HTML - <div> and custom CSS to style your tooltip using this attribute.
  • theme: this attribute helps you maintain a consistent design across all your charts. You can define a chart’s cosmetics in one file and include it using theme attribute to have a common design for all your charts. You can learn more about it on this theme manager documentation page.

There are hundreds of attributes that you can use to customize your chart and it is practically impossible to describe everything in this post. So above I have only listed few key ones, and for more information you can visit the chart attributes page for 2D column charts.

More Resources

I tried to cover everything in detail above, but you are bound to face challenges when you try to do it on your own or want to take this concept further. So to help you in your journey, here are some important resources:

  • Documentation: to learn more about drill down charts and its implementation, please visit the official developer center page.
  • PHP wrapper: if you want to know more about what is possible with FusionCharts’ wrapper, you can explore their PHP charts page. It contains many live examples with full code.

Feel free to post your questions/feedback/suggestions in the comment section below. Always happy to chat and help!