Membuat Dashboard Interaktif Drill-Down dgn PHP dan FusionCharts

fankychristian - October 08, 2012

Learn to Create Interactive Drill-Down Dashboards with PHP and FusionCharts

 on Jan 11th 2011 with 36 comments
Tutorial Details
  • Topic: PHP, mySQL
  • Difficulty: Beginner
  • Estimated Completion Time: 60 minutes
DownloadSOURCE FILES
DemoVIEW IT ONLINE
This tutorial will teach you how to easily create drill-down charts in PHP using the LinkedCharts feature of FusionCharts.

Introduction

Presenting data in a graphical format always helps a lot in parsing the data efficiently. If the charts are interactive and linked, that’s even better.
Drill-down charts allow you to convert individual data plots (columns in column chart, pie slices in pie chart etc) of a chart into hotspots (or links).
These items, when clicked, can open new charts and you can navigate between these charts easily.

What are Linked Charts

Linked charts are a new and smart drill-down feature introduced in FusionCharts v3.2
Untiil now, these charts had individual data streams and were not completely tied together. What if all this data could be in one data stream and we could browse through these charts back and forth? Well, that’s exactly what LinkedCharts does. It’s a new and smart drill-down feature introduced in FusionCharts v3.2, that allows you to create unlimited levels of drill-down charts using a single data source. All the links originate from a parent chart and its respective data.
In this tutorial, we are going to take a practical look at integrating a chart with a sample application. The MySQL database will contain the data — a little PHP will work as an intermediary to fetch and process the data — and FusionCharts will handle the process of displaying the charts
Data flow
To learn more about LinkedCharts and drill-down charts, take a look here. To briefly sum it up:
  • Drill-down charts display data with the same settings as the parent chart by default.
  • Additional configuration can also be done per drill-down level. You can change chart type and settings at each level of drill-down.
  • Open links in a separate HTML container, jQuey dialog, lightbox, extJS window and more.
  • Extensive events support using Javascript.

What are We Building?

Often times, we come across a situation where we want to monitor the growth of new users on our website. With that in mind, let’s make a small application that will show the number of registered users monthly / daily / hourly. Or, in simpler terms: how many users signed up in a given period of time?
We’ll make a simple bar chart, which is linked in such a way so that, initially, a monthly chart is shown. When someone clicks on one of the monthly bars, it changes to the daily chart, and again, subsequently, to the daily chart.

Requirements

  • Any web server running PHP.
  • MySQL database server, though, you can use any database of your choice.
  • FusionCharts. If you do not have it, you can download an evaluation copy or buy it.

Step 0: Initial Setup

  • Create an empty database, named fctutorial. You can also use an existing database, but be careful of overwriting tables.
  • Create an empty folder fcdemo in your web-server’s root directory (usually www).

Step 1: Preparing the Database

To keep it simple, we will use one table, users, which will contain the user information. We are only interested in the signup time of the users (the time they registered). So, we will create a table with only that information. Use the following SQL query to do so.
view plaincopy to clipboardprint?
  1. CREATE TABLE `users`  
  2. (  
  3.   `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  4.   `Time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
  5.   PRIMARY KEY (`ID`),  
  6.   KEY `Time` (`Time`)  
  7. )  
We have created a table with two fields: ID and time. This time field contains the signup time of the users. Note that we have applied an INDEX on the time field, because we will be doing a lot of WHERE clause operations on it. Indexing helps performance in these situations.

Connect to the Database

Let’s write a quick and small script that will help us to connect to database.
view plaincopy to clipboardprint?
  1. <?php  
  2. $db_host     = 'localhost';  
  3. $db_database = 'fctutorial';  
  4. $db_username = 'root';  
  5. $db_password = '';  
  6. if ( ! mysql_connect($db_host, $db_username, $db_password))  
  7.     die ("Could not connect to the database server.");  
  8. if ( ! mysql_select_db($db_database))  
  9.     die ("Could not select the database.");  
  10. ?>  
Change the database credentials according to your own settings. Next, save this file as connect-to-database.php in the fcdemo folder we created.

Inserting Sample Data

In a real world application, the users table will be populated over time. But, for demonstration purpose, we need to have some data in it. Let’s use a simple script to insert some random data into the table. Don’t worry if you don’t understand the following code; it’s not essential to this tutorial.
view plaincopy to clipboardprint?
  1. <?php  
  2. //Connect to database  
  3. require 'connect-to-database.php';  
  4. //Set variables  
  5. $MinTime = strtotime('2010-01-01');  
  6. $MaxTime = strtotime('2010-12-12');  
  7. $RecordsToInsert = 10000;  
  8. //Generate random time and insert records  
  9. for($i = 0; $i < $RecordsToInsert; $i++)  
  10. {  
  11.     $RandomTime = rand($MinTime, $MaxTime);  
  12.     mysql_query("INSERT INTO `users` (Time) VALUES (FROM_UNIXTIME({$RandomTime}))") or die(mysql_error());  
  13. }  
  14. //Completed  
  15. echo "Inserted {$RecordsToInsert} records.";  
  16. ?>  
Save this file as generate-random-data.php in the fcdemo folder.
First, we connect to the database using the small script we wrote, then we set the minimum and maximum time from which randomly a time will be picked and inserted into the users table. Also, you can change the number of records to insert by changing $RecordsToInsert.
Next, we run this script to insert the data into the database. To do so, open your web browser and go to this URL :http://localhost/fcdemo/generate-random-data.php.
You should see Inserted 10000 records as the output.

Step 2: Preparing the Skeleton HTML

Now, the actual process begins. We will create a basic HTML page that will hold the area where the chart will be shown. Here’s the basic HTML markup we will be using:
view plaincopy to clipboardprint?
  1. <html>  
  2.   <head>  
  3.     <title>FusionCharts v3.2 - LinkedCharts with PHP Demo</title  
  4.     <script type="text/javascript" src="Charts/FusionCharts.js"></script>  
  5.   </head>  
  6.   <body>  
  7.     <div id="chartContainer">FusionCharts will load here</div>  
  8.     <script type="text/javascript"><!--  
  9.       var myChart = new FusionCharts( "Charts/Column3D.swf", "myChartId", "800", "400");  
  10.       myChart.setXMLUrl( "get-data.php?year=2010" );  
  11.       myChart.render( "chartContainer" );  
  12.       // -->  
  13.     </script>  
  14.   </body>  
  15. </html>  
  16.     
Save this file as demo.html. What we have done is:
  • In <head>, we have imported the JavaScript file that is supplied with FusionCharts asFusionCharts.js. You can find it in the Charts folder in the FusionCharts zip file.
  • In the <body>, we have created a <div> element which will hold the chart.
  • Finally, we have written a small JavaScript snippet that essentially creates a FusionChart object with the following parameters:
    a) Charts/Column3D.swf specifies the FusionCharts chart SWF file to be used. For this demo, we are using the Column3D chart.
    b) myChartId tells the internal ID of the chart. This is helpful if we are creating more than one chart and need to reference them later.
    c) The next two parameters, 800 and 400, are the width and height of the chart.
  • The myChart.setXMLUrl( "get-data.php?year=2010" ); bit defines the data source. Here, we tell it that the chart data will be XML and the URL of the data is get-data.php?year=2010. The get-data.php is the script we will be writing in the next section.
So, we have used two files from the FusionCharts package. You need to copy and past them in thefcdemo/Charts folder to make our app work.

Step 3: Writing the Backend

FusionCharts requires a source data in XML or JSON format. Let’s use the well-known XML data format, for this particular demo. The data file can contain both data for plotting, and cosmetic/functional configuration for the chart. Our objective is to create PHP code that can read the data from the database and generate an XML file like so:
view plaincopy to clipboardprint?
  1. <chart caption="Monthly New Users for the Year: 2010" xAxisName="Months" yAxisName="Users" showNames="1" bgColor="E6E6E6,F0F0F0" bgAlpha="100,50" bgRatio="50,100" bgAngle="270" showBorder="1" borderColor="AAAAAA" baseFontSize="12">  
  2.     <set value="486" name="1"/>  
  3.     <set value="443" name="2"/>  
  4.     <set value="553" name="3"/>  
  5.     <set value="550" name="4"/>  
  6.     <set value="634" name="5"/>  
  7.     <set value="622" name="6"/>  
  8.     <set value="710" name="7"/>  
  9.     <set value="772" name="8"/>  
  10.     <set value="850" name="9"/>  
  11.     <set value="1044" name="10"/>  
  12.     <set value="1175" name="11"/>  
  13.     <set value="761" name="12"/>  
  14. </chart>  
The XML is simply an XMLized version of data as normally shown in tabular format. The chart tag encapsulates all the data. The attributes in the opening tag here specify a few of the many possible disaply options, which instructs the underlying SWF file how to show the data. Here, we have specified the X and Y axis names, background colors, etc.
So, we will create the get-data.php file that will query the database for users, parse the results and create the XML file.
view plaincopy to clipboardprint?
  1. <?php  
  2. //Sanitizing the input  
  3. $Year   = intval($_GET['year']);  
  4. //Query to get users count for the give year.  
  5. $Query = "SELECT MONTH(Time) AS Value, COUNT(*) AS Total FROM `users` WHERE YEAR(Time)={$Year} GROUP BY Value";  
  6. // fill the Result array with 0 values for each month  
  7. $ResultArray = array_fill(1, 12, 0);  
  8. // Prepare Chart Heading and X-AXIS label.  
  9. $ChartHeading = 'Monthly New Users for the Year: '.$Year;  
  10. $XaxisName = 'Months';  
  11. //Connect to database  
  12. require 'connect-to-database.php';  
  13. //Query the database  
  14. $QueryResult = mysql_query($Query);  
  15. //Fetch results in the Result Array  
  16. while($Row = mysql_fetch_assoc($QueryResult))  
  17.     $ResultArray[$Row['Value']]=$Row['Total'];  
  18. //Generate Chart XML: Head Part  
  19. $Output = '<chart caption="'.$ChartHeading.'" xAxisName="'.$XaxisName.'" yAxisName="Users" showNames="1" bgColor="E6E6E6,F0F0F0" bgAlpha="100,50" bgRatio="50,100" bgAngle="270" showBorder="1" borderColor="AAAAAA" baseFontSize="12">';  
  20. //Generate Chart XML: Main Body  
  21. foreach($ResultArray as $key => $val)  // key is month number (1-12)  
  22.     $Output .= '<set value="'.$val.'" name="'.$key.'"/>';  
  23. //Generate Chart XML: Last Part  
  24. $Output .= '</chart>';  
  25. //Set the output header to XML  
  26. header('Content-type: text/xml');  
  27. //Send output  
  28. echo $Output;  
  29. ?>  
Save this file as get-data.php. Let’s go over the code, bit by bit:
  • We sanitize and pass the value of $_GET['year'] in the $Year variable.
  • The query we are using is a simple query that counts the number of users registered, filtered by theWHERE clause to only users that were registered in the year passed by $Year. The result is GROUP BY- the value which is numeric.
  • Next, we prepare $ResultArray by filling it with 0 values for 1 to 12 keys. You’ll learn shortly why we do this.
  • We store the heading of the chart and the X-Axis label in appropriate variables.
  • We call our database script and perform the actual query on the database.
  • Next, we fetch the results, row by row, and insert the data into an array. As the SQL query does not return rows which have no number of users, we want 0 to appear here. That’s why we pre-filled$ResultArray with zeroes. This also assists us when automatically pre-sorting the array.
  • The next line of code deals with the process of creating the head section of the XML. The caption is set as $ChartHeading, which will serve as the heading of the chart. X and Y axis label are also set along with some color and formatting attributes.
  • Now, the main body of the XML file is prepared and stored in the $Output variable. It’s nothing but the simple name and value attributes in the <code><set> tag .
  • To complete the XML output, we close the <chart> tag.
  • As our output is XML, we set the content-type header as XML and echo the contents of our output variable.
Let's see what XML is generated by this code. Open your web browser and visit
http://localhost/fcdemo/get-data.php?year=2010.
You should see XML data, with contents similar to what was shown at the beginning of this step.

Test it Out

Now that everything has been created, let's test out what we've created. Open your web browser and go tohttp://localhost/fcdemo/demo.html.
If you have followed everything correctly, you will see a working column3D chart with the monthly data.
Monthly Data

Step 4: Converting it to a Drill-Down Chart with LinkedCharts

Our real objective today is to make our chart clickable, so that when someone clicks on any of the bars within the graph, the chart transitions, accordingly, to the daily chart.
There are two required methods to embed the child charts data into parent charts. We can append either the whole data string or just the data URL for all the child charts within the parent data source. If data string is used, the data for each descendant chart is embedded within the parent data source and linked using unique data identifiers. However, as in our case, there are many possible child charts (12 months x 30 days x 24 hours). In our case, we will use the data URL method. This way, we only need to link the data URL to the hotspots (the points which can be clicked, e.g. the bars in our chart).

Modifying the PHP Code:

As we need the PHP code to fetch and generate XML for three kinds of chart: monthly, daily and hourly, we need to modify our existing code. Let's see the code first, and then we will dissect it piece by piece:
view plaincopy to clipboardprint?
  1. <?php  
  2. //Sanitizing the input  
  3. $Type  = $_GET['type'];  
  4. $Year  = intval($_GET['year']);  
  5. $Month = intval($_GET['month']);  
  6. $Day   = intval($_GET['day']);  
  7. //Months Names  
  8. $MonthsNames = array(null, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');  
  9. //Prepare variables according to type-of-chart  
  10. switch($Type)  
  11. {  
  12.     default:  
  13.     case 'monthly':  
  14.         $Query = "SELECT MONTH(Time) AS Value, COUNT(*) AS Total FROM `users` WHERE YEAR(Time)={$Year} GROUP BY Value";  
  15.         $ResultArray = array_fill(1, 12, 0); // fill the Result array with 0 values for each month  
  16.         $ChartHeading = 'Monthly New Users for the Year: '.$Year;  
  17.         $XaxisName = 'Months';  
  18.         break;  
  19.     case 'daily':  
  20.         $Query = "SELECT DAY(Time) AS Value, count(*) AS Total FROM `users` WHERE YEAR(Time)={$Year} AND MONTH(Time)={$Month} GROUP BY Value";  
  21.         $ResultArray = array_fill(1, 31, 0);  // fill the Result array with 0 values for each day  
  22.         $ChartHeading = 'Daily New Users for the Month: '.$MonthsNames[$Month].'/'.$Year;  
  23.         $XaxisName = 'Days';  
  24.         break;  
  25.     case 'hourly':  
  26.         $Query = "select HOUR(Time) AS Value, count(*) AS Total FROM `users` WHERE YEAR(Time)={$Year} AND MONTH(Time)={$Month} AND DAY(Time)={$Day} GROUP BY Value";  
  27.         $ResultArray = array_fill(0, 24, 0);   // fill the Result array with 0 values for each hour  
  28.         $ChartHeading = 'Hourly New Users for the Date: '.$Day.'/'.$MonthsNames[$Month].'/'.$Year;  
  29.         $XaxisName = 'Hours';  
  30.         break;  
  31. }  
  32. //Connect to database  
  33. require 'connect-to-database.php';  
  34. //Query the database  
  35. $QueryResult = mysql_query($Query);  
  36. //Fetch results in the Result Array  
  37. while($Row = mysql_fetch_assoc($QueryResult))  
  38.     $ResultArray[$Row['Value']]=$Row['Total'];  
  39. //Generate Chart XML: Head Part  
  40. $Output = '<chart caption="'.$ChartHeading.'" xAxisName="'.$XaxisName.'" yAxisName="Users" showNames="1" bgColor="E6E6E6,F0F0F0" bgAlpha="100,50" bgRatio="50,100" bgAngle="270" showBorder="1" borderColor="AAAAAA" baseFontSize="12">';  
  41. //Generate Chart XML: Main Body  
  42. switch($Type)  
  43. {  
  44.     default:  
  45.     case 'monthly':  
  46.         foreach($ResultArray as $MonthNumber => $value)  // MonthNumber is month number (1-12)  
  47.             $Output .= '<set value="'.$value.'" name="'.$MonthsNames[$MonthNumber].'" link="newchart-xmlurl-get-data.php?type=daily&year='.$Year.'&month='.$MonthNumber.'"/>';  
  48.         break;  
  49.     case 'daily':  
  50.         foreach($ResultArray as $DayNumber => $value)  // DayNumber is day (1-31)  
  51.             $Output .= '<set value="'.$value.'" name="'.$DayNumber.'" link="newchart-xmlurl-get-data.php?type=hourly&year='.$Year.'&month='.$Month.'&day='.$DayNumber.'"/>';  
  52.         break;  
  53.     case 'hourly':  
  54.         foreach($ResultArray as $HourNumber => $value)  // HourNumber is hour (0-23)  
  55.             $Output .= '<set value="'.$value.'" name="'.$HourNumber.'"/>';  
  56.         break;  
  57. }  
  58. //Generate Chart XML: Last Part  
  59. $Output .= '</chart>';  
  60. //Set the output header to XML  
  61. header('Content-type: text/xml');  
  62. //Send output  
  63. echo $Output;  
  64. ?>  
Now, to understand the changes:
  • As earlier, we are sanitizing the input. Now, we have three kinds of charts, so the GET variable will tell us the kind of chart requested. It can be either monthly, daily or hourly. Similarly, we have $Monthand $Hour, in addition to $Year.
  • Next, we create an array of $MonthNames that will help us when converting the month number to its respective name easily. As an array is zero-based, and a month starts with 1, we set the first array item to null.
  • Now, depending on the type, the value of the $Query, $ResultArray, $ChartHeading and$XAsixName variables are set, using the switch statement. If $Type is undefined, the default case selects the monthly chart.
  • Next, we connect to the database, execute the query, fetch the results in the $ResultArray, and generate the first part of the XML file.
  • The main body of the XML is generated next. However, as it depends on the chart type, we have another switch statement. The real difference lies here:
    • Monthly: Everything is the same, except for the link attribute. The link has the structure:newchart-xmlurl-get-data.php. Here, newchart denotes that a new chart will be created.xmlurl denotes that the data for the new chart is to be fetched from a URL (called the data URL). Whatever comes next becomes the part of this URL. The full link for the January bar becomes: newchart-xmlurl-get-data.php?type=hourly&Year=2010&Month=1
    • Daily: It follows the same structure as the monthly part above. The link attribute now has a URL that has a variable for the day along with others.
    • Hourly: As we do not want hourly chart to be clickable, the link attribute is not used here.
  • Finally, the <chart> tag is closed and the XML header is sent.
Save the file as get-data.php. Now, open your web-browser and check if everything is working correctly. Go to http://localhost/fcdemo/get-data.php?year=2010.
You should see XML data similar to this:
view plaincopy to clipboardprint?
  1. <chart caption="Monthly New Users for the Year: 2010" xAxisName="Months" yAxisName="Users" showNames="1" bgColor="E6E6E6,F0F0F0" bgAlpha="100,50" bgRatio="50,100" bgAngle="270" showBorder="1" borderColor="AAAAAA" baseFontSize="12">  
  2.     <set value="486" name="Jan" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=1"/>  
  3.     <set value="443" name="Feb" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=2"/>  
  4.     <set value="553" name="Mar" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=3"/>  
  5.     <set value="550" name="Apr" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=4"/>  
  6.     <set value="634" name="May" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=5"/>  
  7.     <set value="622" name="Jun" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=6"/>  
  8.     <set value="710" name="Jul" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=7"/>  
  9.     <set value="772" name="Aug" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=8"/>  
  10.     <set value="850" name="Sep" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=9"/>  
  11.     <set value="1044" name="Oct" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=10"/>  
  12.     <set value="1175" name="Nov" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=11"/>  
  13.     <set value="761" name="Dec" link="newchart-xmlurl-get-data.php?type=daily&year=2010&month=12"/>  
  14. </chart>  

Test it out

Now that we have modified it, let's test out what we have created. Open your web browser and go tohttp://localhost/fcdemo/demo.html.
If you have followed the steps above correctly, you will see a working Column3D chart. When any of the monthly bars is clicked, the chart will transition to a daily chart for selected month. Subsequently, the same applies to the days as well.
A nifty added feature is that you can use the back button of your browser to return to the previous chart.
Daily Chart Output

The Result

Here is a video which details how our completed application should look:

Conclusion: Extend & Experminent

If you followed this tutorial all the way through, you should now have a decent understanding of what it takes to create a simple LinkedCharts web application with PHP/MySQL. Similar charts and dashboards can be made for:
  • How many products sold over a period of time?
  • How many times your website went down?
  • How many times a particular web page was accessed?
The basic structure is the same for each of these scenarious: have a Time field in your table, and use that data to build the XML. Finally, fetch this XML to the chart.
Take a look at some of these stunning dashboard demos for more inspiration. Thank you for reading, and, if you have any questions or comments about this tutorial, please post!
Tags: