The Oracle + PHP Cookbook

Flash Remoting with Oracle and PHPObject


by Nick Bollweg

Use PHPObject to integrate Macromedia Flash seamlessly with your Oracle data.

Downloads for this article:
 Sample code and listings
 Oracle Database 10g Express Edition
 Zend Core for Oracle
 PHPObject by Ghostwire Studios
 30-Day Trial of Macromedia Flash

Published January 2006

Your users want it, your manager wants it, and—getting right down to it—low-latency, interactive applications right in your browser are really cool. With the proliferation of Ajax frameworks and next-generation browsers, you can whip up the next Google Maps in a matter of minutes.

Ajax and the XMLHttpRequest are great, as are the applications that leverage them. However, if you want to roll out a polished rich-media application using data from your Oracle database in PHP, Flash and PHPObject can get you there quickly with fewer browser capability headaches. In this recipe of The Oracle-PHP Cookbook, I'll explain how.

FlashJAX

With the rising popularity of Ajax-enabled applications, the uncertainties associated with the Adobe-acquired Macromedia, and the potential of up-and-coming SVG, deploying new Web applications with Flash is not the buzzword-approved method of the day. However, Flash remains the most accessible platform for high-interactivity Web applications available, with a projected penetration (according to Macromedia) of 97.3%. No browser, and certainly no Ajax framework, can claim this level of popularity.

Flash remoting is a technology built into the Flash player core that enables seamless data transport between the server and the client. (Macromedia offers a compelling, but somewhat server-limited product in that area.) Several open source options exist as well, including AMFPHP and PHPObject. Both approaches operate by passing a serialized object between the user's Flash player and your Web server AMFPHP decodes Macromedia's AMF format on the server side, whereas PHPObject—the subject of this recipe due to its relative maturity—decodes PHP objects on the client side. Both are also capable of consuming SOAP Web services, although that approach lies outside our scope here.

Getting Started

For this recipe, you will need an appropriate Oracle-enabled Web server and Oracle Database 10g. The easiest, most robust way to acquire both is by downloading Zend Core for Oracle, which provides out-of-the-box Oracle and PHP connectivity, and Oracle Database 10g Express Edition , Oracle's free starter database. Furthermore, you will need to download the most recent version of GhostWire Studios' PHPObject (Version 1.52 is used here.) After installing PHP and Oracle, create a directory to house this application in your document root and extract the files from your sample code download. The directory I've used is called "oclflash." Two files from the "server" directory in the PHPObject need to be extracted here as well: Gateway.php and config.php.

As for desktop applications, a Microsoft Windows or Apple Macintosh desktop environment and a recent version of the Flash development environment are necessary. In addition, I recommend the use of SE|PY, an open-source ActionScript editor, although many popular editors also offer ActionScript plug-ins.

In the Flash environment, you will need to make the PHPObject package, written in ActionScript, available so it can be included in your Flash movie. After installing Flash, you should also find a shortcut to "Macromedia Extension Manager" in the folder along with the Flash shortcut. Launch this application, and select File->Install Extension... .Navigate to where you've extracted the PHPObject install file, and select PHPObject_AS2.mxp. Now your Web server and development environment are ready.

Do What Counts

The example in this recipe is a reporting application that allows a user to intuitively navigate a complex set of data, a great use of Flash remoting. (See Figure 1.) Banner ads and simple navigation probably don't require this approach, but a very robust navigation client would be a good fit.

figure 1
Figure 1 Sample application—widget sales chart

To fuel this example, you will extend the pithy HR schema, so beloved by cookbook recipe writers, with the following table to track each sale of the ubiquitous Widget. The definition of this table and a supporting sequence is included in the sample code as createSaleTableAndSequence.sql, to be run in HTML DB or SQL*Plus.

CREATE TABLE SALE ( 
        SALEDATE DATE NOT NULL , 
        SALESPERSON NUMBER NOT NULL , 
        INVOICE NUMBER, PRIMARY KEY (INVOICE) VALIDATE );
Another script, generateTestData.php (included in the sample code download), will generate some random sales data. Run it in your browser (or from the command line, if you prefer).

From this data, you will create a bar chart with flexible drill-down and summary functionality along time and personnel dimensions. The parameters of the chart will be stored in a single SQL query itself, so adding or removing dimensions requires just a few changes to that query. Because the table has been populated with data, if you've made your application available at http://localhost/oclflash, you can test the application now by opening http://localhost/oclflash/chart.html.

Blurring the Line

With the goal of creating a reusable Flash charting application, all chart-specific information resides in one SQL query via verbose column names. This method incurs extra data passing between PHP and Oracle, but it also allows for the rapid addition of new charting dimensions through modification of one query.

In the query below:

select
        "Widget Sales",
        "_Time_Year","_Time_Quarter",
        to_char(to_date("_Time_Month",'MM'),'Month') "_Time_Month",
        "_People_Manager",
        "_People_Salesperson",
        "__Time_Year",  "__Time_Quarter","__Time_Month","__People_Manager","__People_Salesperson"
from
(select 
        count(*)                                "Widget Sales", 
        to_char(SALEDATE,'YYYY')                "_Time_Year",
        to_char(SALEDATE,'Q')           "_Time_Quarter",
        to_char(SALEDATE,'MM')          "_Time_Month",
        b.LAST_NAME                             "_People_Manager",
        a.LAST_NAME                             "_People_Salesperson",
        grouping(to_char(SALEDATE,'YYYY'))"__Time_Year",
        grouping(to_char(SALEDATE,'Q'))         "__Time_Quarter",
        grouping(to_char(SALEDATE,'MM'))        "__Time_Month",
        grouping(b.LAST_NAME)                   "__People_Manager",
        grouping(a.LAST_NAME)                   "__People_Salesperson"
from
        SALE, EMPLOYEES a, EMPLOYEES b
where
        a.EMPLOYEE_ID = SALESPERSON AND
        b.EMPLOYEE_ID = a.MGR
group by
        cube(
                to_char(SALEDATE,'YYYY'),
                to_char(SALEDATE,'Q'),
                to_char(SALEDATE,'MM'),
                b.LAST_NAME,
                a.LAST_NAME
        )
order by
        "_Time_Year","_Time_Quarter","_Time_Month","_People_Manager","_People_Salesperson")
...the following conventions are used:
  • Dimension column names: _<name of dimension>_<name of dimension level>. The example's two dimensions are Time and People, with respective dimension levels like Month and Manager. The order in which a column appears is significant. The broadest level comes first: Year is broader than Quarter, Manager is broader than Salesperson.
  • Summary Column names: __<name of dimension>_<name of dimension level>. An extra underscore will let the code know that this column describes the summary function (grouping) of a row.
(If you are unfamiliar with the CUBE analysis operator—which does most of the heavy lifting in this query—and its special SQL conventions like GROUPING, you may wish to consult the documentation.)

Extending this query entails adding new dimension level value columns and corresponding GROUPING columns. For example, to enable fine-grained analysis of the data be weeks, the following changes would be necessary:

  1. Add to_char(SALEDATE,'W') "_Time_Week" to the inner select. Note the single underscore.
  2. Add to_char(SALEDATE,'W') to the GROUP BY CUBE clause.
  3. Add grouping(to_char(SALEDATE,'W')) "__Time_Week" to the inner select. Note the double underscore.
  4. Add "_Time_Week" to the outer ORDER BY clause.
  5. Add "_Time_Week" to the outer select.
Similarly, you could add a whole new dimension, Space, by following the steps above for "_Space_Location", "_Space_Region", and "_Space_Country", as well as some joins to the appropriate table. Each additional dimension will double the number of rows returned to PHP once per session. Since the client will only be inspecting a small segment of the data at a given time, though, the user will notice just a somewhat longer initial wait time.

With query in hand (or text editor as the case may be), you're ready to look at the PHP side of this example.

The Key to the Remoting Gateway

PHPObject uses a file called Gateway.php to handle all the details of moving serialized objects between PHP and Flash, though you probably won't need to modify this script. However, config.php includes several important values. The "secret key," used for rudimentary authentication, must be available both here and in your client-side Flash movie. The location of class files available for PHPObject, in this case ChartData.php, is also stored here. In the example, everything is in one directory, so no special value need be specified.

$cfg['classdir'][0]     = "";
$cfg['useKey']          = "secret";
An object for PHPObject is implemented as a class with functions that are called directly within the ActionScript that drives your movie; this is the real beauty of the PHPObject model. Below is a snippet from ChartData.php.
function updateChart(){

        $this->chartValues = array_values(array_filter($_SESSION['dataSet'],
                                        array("ChartData","filterDataSet")));                   

        $this->determineLabelColumn();  

}
Below is a snippet of the invocation of the method, as defined in PHP, in the ActionScript 2.0 script chartExampleActions.as.
cd.updateChart();
That's it! At least, that's the easy part, made so through the PHPObject remoting model.

ChartData.php In a Nutshell

When the constructor is called the general behavior of the ChartDataclass is to load the report dataSet from the database, place it in session, and populate the chart data passed to Flash with some default data. On successive calls to updateChart with updated chartParams, ChartData returns different slices of the dataSet.

Some auxiliary functions filter the dataSet and create the topology that will allow the client to drill down and summarize. Among the class variables is the database connection information; you may have to change these to match your local environment.

chartExampleActions.as In a Nutshell

As external ActionScript 2.0 scripts, much like Java class files, may only implement classes and import other classes, a wrapper class defines the behaviors the movie should exhibit. From the perspective of code reuse and change control, this pattern is preferable to spreading little bits of code across a Flash movie.

One exception is animation effect-specific code; such code is usually brief and drives purely aesthetic functions of the movie. In our example, the movieClip "bar" has some attached ActionScript to produce the "growing bars" effect. The wrapper class in the example, chartExampleActions, has the following important points:

PHPObject.defaultGatewayKey = "secret";
PHPObject.defaultGatewayUrl = "http://localhost/oclflash/Gateway.php";
_root.chartData = new PHPObject("ChartData");
This sets the "secret key" and the location of the gateway and creates an instance of ChartData, making it available to the Flash movie. As soon as the constructor successfully completes in PHP, it will trigger the onInit method you define for your instance:
_root.chartData.onInit = function() {
        chart.throbber._visible = false;
        chart.chartTitle.text = this.valueColumn;
        for(var a in this.axes){
                _root.axis.addItem(a);
        }
        drawChart(this);
}
Similarly, on successive calls to updateChart, ChartData fires the onResult method:
_root.chartData.onResult = function() {
        _root.chart.throbber._visible = false;
        drawChart(this);
}
The other important function, drawChart, does the work of actually building the chart, adding logic and interactivity. With the chart.fla file open in the Flash environment, choosing File->Publish... will create an updated version of the swf in in your application directory. This will be necessary if you wish to serve application from a different location than the sample code is expecting.

On a Need-to-Know Basis

As I mentioned previously, PHPObject passes a synchronized copy of an object between Flash and PHP. Since many queries can result in large datasets, it is not always desirable to make these available to Flash. As you are only showing a slice of the results at a time, you can store the rest of data in the PHP session after retrieving it from Oracle. It will be available when next the server processes the object, but won't be passed to the client. Make sure you either have session_autostart enabled on your server or specify session_register() within your classfile—a more precise method and the one used here.

Furthermore, you can hide class functions from Flash (they won't be callable by the client) by setting an explicit list of client-visible functions. In our example, you only want to expose updateChart:

public $classMethods = array("updateChart");
By the way, remember that any remoting solution introduces some security risk to an application. No exhaustive checking is performed of the serialized object returned to PHP by Flash, an advantage of AMFPHP. A PHPObject class, like any regular PHP script, should never implement a means of executing arbitrary SQL on your database.

Conclusion

Flash provides a compelling means of deploying interactive Web elements, and using PHP with PHPObject serves as an efficient means of integrating Oracle data into an interactive component. Before immediately jumping on another interactivity bandwagon, try this one; it offers a great balance of development ease and browser support. Nick Bollweg [ nick.bollweg@gmail.com] is a Senior Programmer at the Sidney Kimmel Cancer Center at Johns Hopkins Medical Institute in Baltimore, Maryland.

Send us your comments