Building Dashboards With PHP and Flex

Let's face it: Interactive graphs and dashboards have never been easy to put together on the web. Sure, there are graphing libraries out there for PHP, but to get something that looks really good and that a user can play with has been tough. Or at least, it was yesterday. Today, I show how to use a combination of PHP for the back end and Adobe Flex for the front end that will put interactive 3D within your grasp. Right now. Today. Let's dig in. To start, I needed some data. So, I put together a simple database called traffic that has one table called traffic that lists the number of page views and such for each day. The simple MySQL schema is shown in Listing 1.

Listing 1. traffic.sql

DROP TABLE IF EXISTS traffic;

CREATE TABLE traffic (
	day DATE,
	users INT,
	views INT,
	pages INT,
	xmlpages INT
);

There are five fields: the day of the sample, the number of users who logged in, the number of page views, the number of real pages served, and the number of XML pages. (I'm just making stuff up here: You can use whatever fields you want.) Now, to display some data, I populate the table with something. To do that, I run the loader.php script shown in Listing 2.

Listing 2. loader.php

<?php
require_once("MDB2.php");

$dsn = 'mysql://root@localhost/traffic';
$mdb2 =& MDB2::factory($dsn);

$dsth =& $mdb2->prepare( "DELETE FROM traffic" );
$dsth->execute( array( ) );

$sth =& $mdb2->prepare( "INSERT INTO traffic VALUE (?,?,?,?,?)" );

$users = 100;
$views = 10000;
$pages = 5000;
$xmlpages = 300;
for( $d = 1; $d <= 30; $d++ ) {
	$date = "2008-04-".$d;
	$sth->execute( array( $date, $users, $views, $pages, $xmlpages ) );
	$users += ( rand( 20, 100 ) - 30 );
	$views += ( rand( 200, 1000 ) - 300 );
	$pages += ( rand( 100, 500 ) - 150 );
	$xmlpages += ( rand( 60, 300 ) - 90 );
}
?>

This script just connects to the database, deletes all the data that's currently in the table, and makes up a month's worth of data randomly. To ensure that the graphs don't look like signal noise, I use a rolling type of random that just shifts each data a random amount. And it's weighted always to go upward. I am optimistic, after all! Now, with the data generated and in the database, I need a way to get to it. The first way I show is through XML using the traffic.php page shown in Listing 3.

Listing 3. traffic.php

<?php
require_once("MDB2.php");

$dsn = 'mysql://root@localhost/traffic';
$mdb2 =& MDB2::factory($dsn);

$dom = new DomDocument();
$dom->formatOutput = true;

$root = $dom->createElement( "traffic" );
$dom->appendChild( $root );

$sth =& $mdb2->prepare( "SELECT * FROM traffic ORDER BY day" );
$res = $sth->execute( $id );
while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) {
	$dn = $dom->createElement( "day" );
	$dn->setAttribute( 'day', $row['day'] );
	$dn->setAttribute( 'users', $row['users'] );
	$dn->setAttribute( 'views', $row['views'] );
	$dn->setAttribute( 'pages', $row['pages'] );
	$dn->setAttribute( 'xmlpages', $row['xmlpages'] );
	$root->appendChild( $dn );
}		

header( "Content-type: text/xml" );
echo $dom->saveXML();
?>

This page connects to the database using the PEAR::MDB2 module and fetches all the data. It then creates a DomDocument object and adds the data to it. Yes, it might have been easier to use simple PHP text formatting to build the XML. But I prefer to do it using the DomDocument object, because the code is easier to read and I never run into XML encoding problems. When I run this script on the command line, it looks like this:

% php Traffic.php
<?xml version="1.0"?>
<traffic>
  <day day="2008-04-01" users="100" views="10000" pages="5000" xmlpages="300"/>
  <day day="2008-04-02" users="97" views="10310" pages="5203" xmlpages="408"/>
  <day day="2008-04-03" users="90" views="10638" pages="5254" xmlpages="453"/>
  <day day="2008-04-04" users="102" views="10932" pages="5271" xmlpages="578"/>
...
</traffic>

Perfect. Now I have an XML data source that I can hook up to Flex.

Building the Flex interface, version 1

Let's be honest: Who really has the time or inclination to build a three-dimensional graphing library on their own? So, let's get one off the shelf. The one I've chosen for this example is the Elixir library from ILOG. It is a commercial product, but it has a trial version that you can download and play with for at no cost. Building the Flex application starts with creating a Flex project in Adobe Flex Builder version 3. Web browser-based or Adobe AIR-based projects are available, depending on whether you want it on the web or on the desktop: either will work. Next, I go to the Project Properties dialog box and click the Flex Build Path tab. From there, I click Library Path, and then click Add SWC to add references to the ILOG Elixir libraries. There are two: the base control classes and the localization library for either English or Japanese. (Your choice on the latter.) The result should look something like Figure 1.

Figure 1. Adding the Elixir libraries to the project

When Elixir is hooked in, I can create the Flex application code that connects to my XML data set and display it in a three-dimensional graph. This code is shown in Listing 4.

Listing4. traffic.mxml

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="horizontal"
  xmlns:ilog="http://www.ilog.com/2007/ilog/flex" creationComplete="trafficReq.send()">
<mx:Script>
<![CDATA[
import mx.rpc.events.ResultEvent;

private var trackPt:Point = null;

private function onTraffic( event:ResultEvent ) : void {
  var days:Array = [];
  for each( var day:XML in event.result..day ) {
    days.push( { day:day.@day.toString(),
      users:parseInt(day.@users),
      pages:parseInt(day.@pages),
      views:parseInt(day.@views),
      xmlpages:parseInt(day.@xmlpages) } );
  }
  chart.dataProvider = days;
}
private function onMouseUp( event:MouseEvent ) : void { trackPt = null; }
private function onMouseMove( event:MouseEvent ) : void {
  if ( trackPt == null ) return;
  chart.rotationAngle += ( event.localX - trackPt.x );
  trackPt = new Point( event.localX, event.localY );
}
private function onMouseDown( event:MouseEvent ) : void {
  trackPt = new Point( event.localX, event.localY );
}
]]>
</mx:Script>
<mx:HTTPService id="trafficReq" resultFormat="e4x" url="http://localhost/traffic/traffic.php" result="onTraffic(event)" />
<ilog:LineChart3D rotationAngle="10" width="100%" height="100%" id="chart" mouseDown="onMouseDown(event)"
  mouseUp="onMouseUp(event)" mouseMove="onMouseMove(event)" showDataTips="true">
<ilog:horizontalAxis>
<mx:CategoryAxis categoryField="day" displayName="Day" />
</ilog:horizontalAxis>
<ilog:series>
<ilog:LineSeries3D xField="day" yField="users" displayName="Users" />
<ilog:LineSeries3D xField="day" yField="pages" displayName="Pages" />
<ilog:LineSeries3D xField="day" yField="views" displayName="Views" />
<ilog:LineSeries3D xField="day" yField="xmlpages" displayName="XML Pages" />
</ilog:series>
</ilog:LineChart3D>
<mx:Legend dataProvider="{chart}"/>
</mx:Application>

The code starts by calling the send method on the trafficReq HTTPService object. This service points to the URL of the PHP page that serves up the XML. When the XML is returned, the onTraffic method is called, which parses the XML into a data set that is ready for the chart object defined at the bottom of the file.

I've defined the chart as LineChart 3D. Several different options are available: area charts, column charts, bar charts, pie charts, and more-all in two or three dimensions. There are also controls that can do mapping, tree charts, Gantt charts, and all manner of charting. The examples that come with the Elixer download will knock your socks off.

Back to the code, I've added some mouse event handlers for down, up, and move that change the rotation angle on the graph. This gives users the ability to pan around a bit. You can play with those methods to perhaps change the viewing angle so that your customer can look from the top down or directly from the side.

When I launch this code in Flex Builder 3, I see something like Figure 2.

Not bad, huh? Just think of what you could pull off when it looks at some real data from your own site.

Now, to dig a little further on both PHP and Flex, let's simplify the data transit.

Getting data with AMF

Flash has a binary data-transmission format called the Action Message Format (AMF). It allows Flex and Adobe Flash applications to send and receive whole objects from the server with calls that resemble standard method calls. To connect that to PHP and to my fake data set, I downloaded and installed AMFPHP. From there, I add a traffic service class to the services directory in the AMFPHP installation directory. The code for that service is shown in Listing 5.

Listing 5. TrafficService.php

<?php
require_once("MDB2.php");
include_once(AMFPHP_BASE . "shared/util/MethodTable.php");
class TrafficService
{
	function getTraffic()
	{
        $dsn = 'mysql://root@localhost/traffic';
        $mdb2 =& MDB2::factory($dsn);
        $sth =& $mdb2->prepare( "SELECT * FROM traffic ORDER BY day" );
        $res = $sth->execute( $id );
        $days = array();
        while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) { $days []= $row; }
        return $days;
	}
}

This code is just like the XML example, the exception being that I don't have to format the data. I just return the data as an array.

To test the code, I bring up the browser included with AMFPHP. This is shown in Figure 3.

Figure 3. Browsing the traffic AMF service

As you can see, you can invoke the getTraffic() method and get back all the records from the database, just like an ActionScript array of objects. Very clean, very fast.

Connecting to AMFPHP

Connecting to the AMFPHP traffic service requires just a few small modifications to the previous examples. These are shown in Listing 6.

Listing 6. Traffic_ro.php

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="horizontal"
  xmlns:ilog="http://www.ilog.com/2007/ilog/flex" creationComplete="trafficRO.getTraffic.send()">
<mx:Script>
<![CDATA[
import mx.rpc.events.ResultEvent;

private var trackPt:Point = null;

private function onTraffic() : void {
  chart.dataProvider = trafficRO.getTraffic.lastResult;
}
...
]]>
</mx:Script>
<mx:RemoteObject id="trafficRO"
  endpoint="http://localhost/amfphp/gateway.php"
  source="traffic.TrafficService" destination="traffic.TrafficService"
  showBusyCursor="true">
<mx:method name="getTraffic" result="onTraffic()" />
</mx:RemoteObject>
<ilog:LineChart3D ...>
...
</ilog:LineChart3D>
<mx:Legend dataProvider="{chart}"/>
</mx:Application>

The HTTPService has been replaced by a RemoteObject that references the AMFPHP server and defines the method I want access to. And the onTraffic method now simply sets the dataProvider field on the chart to the data that's returned from the server. When I run this code in Flex Builder, the result-visually-is just the same as with the original example. The difference is that the code is simpler and that the transit is both faster and smaller than with XML. To enhance the Flex application a bit, I added a slider, which allows a user to view only a subset of the data and adjust that view dynamically. This new code is shown in Listing 7.

Listing 7. traffic_ro2.php

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="horizontal"
  xmlns:ilog="http://www.ilog.com/2007/ilog/flex" creationComplete="trafficRO.getTraffic.send()"
  xmlns:flexlib="flexlib.controls.*">
<mx:Script>
<![CDATA[
import mx.rpc.events.ResultEvent;

private var trackPt:Point = null;
private var days:Array = [];

private function onTraffic() : void {
  days = trafficRO.getTraffic.lastResult as Array;
  dateRange.minimum = 0;
  dateRange.maximum = days.length;
  dateRange.values[0] = 0;
  dateRange.values[1] = days.length;
  chart.dataProvider = days;
}
private function onDateRangeChange() : void {
  chart.dataProvider = days.slice( dateRange.values[0], dateRange.values[1] );
}
...
]]>
</mx:Script>
<mx:RemoteObject id="trafficRO"
  endpoint="http://localhost/amfphp/gateway.php"
  source="traffic.TrafficService" destination="traffic.TrafficService"
  showBusyCursor="true">
<mx:method name="getTraffic" result="onTraffic()" />
</mx:RemoteObject>
<mx:VBox width="100%" height="100%">
<mx:HBox>
<mx:Label text="Date Range" />
<flexlib:HSlider id="dateRange" thumbCount="2" width="300" liveDragging="true" change="onDateRangeChange()"
	snapInterval="1" />
</mx:HBox>
<ilog:LineChart3D ...>
...
</ilog:LineChart3D>
</mx:VBox>
<mx:Legend dataProvider="{chart}"/>
</mx:Application>

I add an HSlider object with two thumbs to the top of the display. When this changes, it calls the onDateRangeChange method to update the chart with only the slice of the data that the user wants to see. This HSlider class comes from the FlexLib library . FlexLib is a set of Flex classes that enhance and extend the original Flex 3 controls. In this case, it allows the user to drag the range between the two thumbs around by clicking between the two.

When I bring this code up in Flex Builder 3 and play with it a bit, it looks like Figure 4.

Figure 4. The remote object graph with a date range selector

In this case, it's showing just the last few days' worth of data. But I can add more days by adjusting either the left or right thumb to whatever I like.

Where to go from here

The combination of technologies I've shown here-PHP, Flex, ILOG Elixir, AMFPHP, and FlexLib-is very strong. ILOG Elixir in particular has an amazing set of visualizations that will complement almost any type of structured data you have and make it look great. It's literally difficult to get Elixir to look bad. If you don't want to pay the money for Elixir, have a look at the charting controls built into Flex or look around on Google for any open source project that fits your needs.

I can't wait to see what you come up with. Let me know at jack@jackherrington.com.