If you can measure it, you can manage it. If you can visualize it too, even better. Businesses and individuals are swimming in data – from social networks, to budgets, to resource planning we are surrounded by tools that generate data and most of us build applications that generate even more data for ourselves and our clients. It can be a challenge to distill and present the data generated by your (or your client’s) business in a way that lets them explore the data to answer questions about their business and make decisions in an informed way.
FusionCharts is a very accessible application that lets you configure and deploy multi-level, drill-down charts to present data in an attractive, simple interface. In this article we’ll walk through a simple implementation of FusionCharts using PHP, MySQL and JSON – you can be up and running in no time using your own data.
When you’re working with drill-down data, you need to start with data that naturally aggregates into meaningful groups. In this example, we’re going to use sales volume over time. The time segments will provide the drill-down – we’ll start in years and drill down from there, the core data will be sales counts at a particular date. Other examples could be geographic data, or data that rolls up (or down) through categories.
Approach and assumptions
For this solution, there will be a combination of custom development integrating a self-contained application, the 3D Column chart from FusionCharts. There are almost 3 dozen chart variants to choose from including scatter plot, line graphs, 2D and 3D options, and so on. FusionCharts provides .swf and .js files that are required for the charts to run, you will have to provide the data and the request/response handlers.
This tutorial assumes you have PHP5 and a mySQL database installed on your web server. A functional understanding of JSON and PHP are required. An understanding of HTML, CSS and JavaScript are also helpful, but less critical for this tutorial. This stack is not required – what you really need is a data source and a handler that can take http requests, access the data, and format a response.
For our purposes though we are going to implement this pretty simply so that any request to our php file will have a JSON response containing all of the data we need.
| Presentation | Column3d.swf embedded in chart-json.html |
| Application controller and data access | linked-chart.php responds with JSON |
| Database | <your database> |
So in our example, any request to chart-json.html will result in the client making request for several assets including javascript files and the swf. When the swf loads it will follow the attributes passed to it in order to load the data, making a request to the php application. That app will access the database, retrieve the data, and format the response. The swf will parse the data contained in the JSON file and build our graph.
Ready to roll? Let’s do it.
Implementing a Linked FusionChart
First, download the core FusionChart JavaScript and Flash files here. Follow the installation instructions found in the index.html file, contained in the zip. LinkedCharts is a feature of FusionCharts that allows you to have unlimited drill-down capabilities where users can click on a data plot item and a child chart will be served up either replacing the current chart or spawned to a child window or frame.
For this tutorial we’ll focus on these files:
/chart-json.html(Click here for code Contains the markup to display the chart. includes the proprietary FusionChart JavaScript and Flash files. invokes linked-chart.php)/linked-chart.php(Click here for code php that connects to the mySQL database and outputs the data as JSON)/FusionCharts.js(Code found in zip file. Proprietary FusionChart file that ingests the JSON and injects the data into Column3D.swf)/Charts/Column3D.swf(Code found in zip file. Displays the user interface to make the data pretty)
First, we’ll need our data. Since our example will work with data that neatly aggregates into portions of the year, we’ll just generate data based on timestamps. We can sort it into groups using SQL later.
SQL to create the database table :
CREATE TABLE 'revenue'
(
'ID' int(10) unsigned NOT NULL AUTO_INCREMENT,
'Time' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY ('ID'),
KEY 'Time' ('Time')
)
Fill the table by way of another php script that will generate 10,000 rows of timestamps over two years that can be used as our sales data over time:
generate-random-data.php
<?php
//Connect to database
$db_host = 'database-url-goes-here';
$db_database = 'database-name-goes-here';
$db_username = 'database-username-goes-here';
$db_password = 'database-password-here';
if ( !mysql_connect($db_host, $db_username, $db_password))
die ("Could not connect to the database server.");
if ( !mysql_select_db($db_database))
die ("Could not select the database.");
//Set variables
$MinTime = strtotime('2010-01-01');
$MaxTime = strtotime('2010-12-12');
$RecordsToInsert = 10000;
//Generate random time and insert records
for($i = 0; $i < $RecordsToInsert; $i++)
{
$RandomTime = rand($MinTime, $MaxTime);
mysql_query("INSERT INTO 'revenue' (Time) VALUES
(FROM_UNIXTIME({$RandomTime}))") or die(mysql_error());
}
//Completed
echo "Inserted {$RecordsToInsert} records.";
?>
Now let’s build out the logic layer. This is the heart of the application since it handles requests and manages the retrieval of the data and the formatting of the response. Since it’s PHP we’ll be doing a lot in one file: establishing the database connection, gathering the objects we’ll need using SQL statement, sorting and filtering the responses, and then formatting the response into JSON.
First we’ll handle the request and define the params we’ll accept in the URI:
<?php //Sanitizing the input $Type = $_GET['type']; $Year = intval($_GET['year']); $Month = intval($_GET['month']);
Next, we’ll set up the array to handle our values for segmenting the names of the months. Then use case statements based on the param in the request to select and filter the data into the aggregates we want to provide for drill-down – in this case months and days. The data returned fills the arrays and will be used later in the JSON response sent to the FusionCharts.js calls.
//Months Names
$MonthsNames = array(null, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');
//Prepare variables according to type-of-chart
switch($Type)
{
default:
case 'monthly':
$Query = "SELECT MONTH(Time) AS Value, COUNT(*) AS Total FROM 'revenue' WHERE YEAR(Time)={$Year} GROUP BY Value";
$ResultArray = array_fill(1, 12, 0); // fill the Result array with 0 values for each month
$ChartHeading = 'Monthly Revenue for the Year: '.$Year;
$XaxisName = 'Months';
break;
case 'daily':
$Query = "SELECT DAY(Time) AS Value, count(*) AS Total FROM 'revenue' WHERE YEAR(Time)={$Year} AND MONTH(Time)={$Month} GROUP BY Value";
$ResultArray = array_fill(1, 31, 0); // fill the Result array with 0 values for each day
$ChartHeading = 'Daily Revenue for the Month: '.$MonthsNames[$Month].'/'.$Year;
$XaxisName = 'Days';
break;
}
Build your database connection and retrieve the data
//Connect to database
$db_host = 'database-url-goes-here';
$db_database = 'database-name-goes-here';
$db_username = 'database-username-goes-here';
$db_password = 'database-password-here';
if ( !mysql_connect($db_host, $db_username, $db_password))
die ("Could not connect to the database server.");
if ( !mysql_select_db($db_database))
die ("Could not select the database.");
//Query the database
$QueryResult = mysql_query($Query);
//Fetch results in the Result Array
while($Row = mysql_fetch_assoc($QueryResult))
$ResultArray[$Row['Value']]=$Row['Total'];
Now that the data has been retrieved and stored in an array, we’ll need to format our JSON response. The first section of JSON will contain information that FusionCharts will use for labels. Based on the level, the JSON will contain more or less data – 12 rows of data for month, and a variable number of rows for daily.
The key to the drill-down feature is in the ‘link’ attribute – by passing the URI newchart-jsonurl-get-data.php?type=daily&year='.$Year.'&month='.$MonthNumber.' FusionCharts will make the region of data a link and when the user clicks on it the appropriate month of drill-down data will be loaded.
Here’s what the php looks like to generate the JSON
//Generate json: parent node
$Output = '{"chart":{"caption":"'.$ChartHeading.'","xaxisname":"'.$XaxisName.'","yaxisname":"Revenue"}, "data":[';
//Generate JSON: inner nodes for monthly and daily view
switch($Type)
{
default:
case 'monthly':
foreach($ResultArray as $MonthNumber => $value) { // MonthNumber is month number (1-12)
$Output .= '{ "value" : "'.$value.'", "label":"'.$MonthsNames[$MonthNumber].'", "link":"newchart-jsonurl-get-data.php?type=daily&year='.$Year.'&month='.$MonthNumber.'" } ';
if ($MonthNumber < count($ResultArray)) {
$Output .= ',';
}
}
break;
case 'daily':
foreach($ResultArray as $DayNumber => $value) { // DayNumber is day (1-31)
$Output .= '{ "value" : "'.$value.'", "label":"'.$DayNumber.'" } ';
if ($DayNumber < count($ResultArray)) {
$Output .= ',';
}
}
break;
}
$Output .= ']}';
//Send output
echo $Output;
?>
Finally, we’ll need that presentation layer and we’ll create an HTML page that will contain the requests we need and the params we want to pass to the LinkedChart.
Code for chart-json.html :
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Linked FusionChart using PHP, JSON and MySQL</title> <script type="text/javascript" src="Charts/FusionCharts.js"></script> </head> <body> <div id="chartContainer">FusionCharts will load here</div> <script type="text/javascript"> <!-- FusionCharts._fallbackJSChartWhenNoFlash(); var myChart = new FusionCharts("Charts/Column3D.swf", "myChartId", "700", "500", "0", "1"); myChart.setJSONUrl("linked-chart.php?year=2010"); myChart.render("chartContainer"); // --> </script> </body> </html>
And here’s the result:
FusionCharts expects the link to be URL encoded but you can override this by using an ‘unescapelinks’ attribute for your chart. As FusionCharts builds the chart, the links are embedded in the columns allowing users to click on a column and drill down to the next level of data, in this case daily.
You can configure the colors and styles and the levels of drill-down are really throttled by the data that you are working with.
This was a pretty simple example, but you could extend this in a lot of ways. You could make the data accessible via a RESTful interface and add parameters to the URI pattern, for instance. What are some ideas you have? This example is using tabular data, what about drill-down geo-demographic data? Let us know.
Rob Frieman & Michael McCarthyRob Frieman has been building web sites and applications for over 15 years. He currently leads web application development for a large financial services company. Michael McCarthy is the founder and leading Webmaster of Web Hosting Reviews, one of the Internet's most valued Web host directories.
