ONLamp.com    
 Published on ONLamp.com (http://www.onlamp.com/)
 See this if you're having trouble printing code examples


Administering MySQL Using Flex

by Jack Herrington
01/15/2008

PHPMyAdmin rocks. Of that there can be no question. It's easily one of the best PHP-based applications, because it trades the clunky command-line interface for MySQL administration for the web browser. But while it's very powerful, it's not very friendly to use or easy on the eyes. So I figured I would play around with using a Rich Internet Application framework to see if I could put a sexier front end on MySQL administration.

For this task, I could have chosen Ajax, but I didn't want to deal with the client-side incompatibility issues. Silverlight is certainly interesting, but it's still early days yet. I chose Adobe Flex because of its rich user interface toolkit and easy web service integration and because it generates Flash applications that run the same way on any operating system.

I learned a lot in building the application: how to build a secure SQL web service for PHP, how to access that web service from Flex, and just how easy it is to hook up data returned from the web service into a data grid for display. In this article, I'll walk you through all the steps to build it from front to back, and you can see whether you find some interesting stuff for your own Rich Internet Application.

Building the Back End

Flex applications like to talk to web services on the server to request and post data. So the first thing I needed to do was build a very simple PHP script that returns a list of databases, tables, or the data from a table as XML.

Listing 1. req.php

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

$sql = 'SHOW DATABASES'; 
if ( $_REQUEST['mode'] == 'getTables' ) 

    $sql = 'SHOW TABLES'; 
if ( $_REQUEST['mode'] == 'getData' ) 
    $sql = 'SELECT * FROM '.$_REQUEST['table']; 

$dsn = 'mysql://root@localhost/'.$_REQUEST['db']; 

$mdb2 =& MDB2::factory($dsn); 
if (PEAR::isError($mdb2)) { die($mdb2->getMessage()); } 

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

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

$res =& $mdb2->query( $sql ); 
if (PEAR::isError($mdb2)) { die($mdb2->getMessage()); } 
while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) 
{ 

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

    foreach( array_keys( $row ) as $key ) { 
        $key_elem = $dom->createElement( $key ); 
        $rec->appendChild( $key_elem ); 
        $key_elem->appendChild( $dom->createTextNode( $row[$key] ) ); 
    } 
} 
$res->free(); 

$mdb2->disconnect(); 

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

The first thing the script does is connect to the database using the MDB2 library. If you don't have the MDB2 library installed, use PEAR to install it like this:

% pear install MDB2 
%

If PEAR doesn't work for you, go to http://pear.php.net/mdb2, then download the source and unpackage it somewhere in the include path for PHP. MDB2 is the generic database adapter layer that has superseded the popular PEAR DB library.

The next thing the script does is create an XML DOM Document object that it will use to create the XML tree for output. From there it runs the query and adds row and column as XML tags in the XML tree. The script finishes up by closing all the connections and saving the XML to the PHP output stream.

I use the XML DOM objects because it ensures that I won't run into any encoding problems with the data or unbalanced tags or any of the other myriad of things that can mess up XML. I have a lot better things to do with my time than to debug XML data streams. I'm sure you do, too.

With that script installed on my local machine in a directory where I can run it, I use the curl command to make a request of the server.

% curl "http://localhost/sql/req.php" 
<?xml version="1.0"?> 
<records> 
    <record> 
        <database>addresses</database>
    </record>
    <record> 
        <database>ajaxdb</database> 
    </record> 
... 
%

In this case, I don't specify a database or a mode, and that tells the script to return the list of available databases. Assuming the web server script has the permission to do such a thing, I see the result as shown after the curl statement. In this case, I get a list of the different databases as tags.

All data returned from the script will have a <records> tag that includes a bunch of <record> tags. The names and contents of each <record> tag will depend on the SQL statement that was run. In this case, only a single column, named database, is returned.

Another option in addition to using curl is to load the URL into the browser, then select View Source after the page has been loaded.

In the next example, I'll connect to the articles database and get its list of tables. You can see the result below:

% curl ".../req.php?mode=getTables&db=articles" 
<?xml version="1.0"?> 
<records> 
    <record> 
    <tables_in_articles>article</tables_in_articles> 
    </record> 
</records> 
%

The articles database contains a single table with the name article, which I suppose isn't too surprising. To run the classic select * from article query to get all the records, I use the URL shown below:

% curl ".../req.php?mode=getData&db=articles&table=article" 
<?xml version="1.0"?> 
<records> 
    <record> 
        <id>1</id> 
        <title>Apple releases iPhone</title> 
        <content>Apple Computer is going to release the iPhone...</content> 
    </record> 
    <record> 
        <id>2</id> 
        <title>Google release Gears</title> 
        <content>Google, Inc. of Mountain View California has...</content> 
        </record> 
</records> 
%

I see there are two records in the table: the first about Apple releasing the super cool iPhone, and the second about Google releasing the also very cool, but for completely different reasons, Gears system.

Now, with this extremely powerful and flexible back end installed on my local machine, it's time to start building a Flex front end for it.

Building the User Interface

I like to build Flex applications in layers, starting with the user interface, then adding the code to the skeleton to flesh it out with interactivity. To get started, first install the Flex SDK if you haven't already. The Flex SDK is free, and there's a free trial version of the Flex Builder 2 IDE. You can download the Flex SDK from http://www.flex.org/download. The Flex 3 SDK will be open source. Flex Builder is an Eclipse-based development environment that you can use to build interfaces by dragging and dropping controls, but you don't have to use it. I'm a tag junky, so I usually stick to the code view and work directly with the MXML.

Now what I want from the interface is a couple of controls at the top and a data grid at the bottom to display the data in the tables. The controls on the top should be two drop-downs where I can select the database and the table.

The MXML for this form is shown in Listing 2.

Listing 2. flexmysql1.mxml

<?xml version="1.0" encoding="utf-8"?> 
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical"> 
<mx:VBox horizontalAlign="left"> 
<mx:HBox> 
    <mx:Label text="Database:" /> 
    <mx:ComboBox id="selectedDatabase" width="381" height="21"> 
    </mx:ComboBox> 
</mx:HBox> 
<mx:HBox> 
    <mx:Label text="Table:" /> 
    <mx:ComboBox id="selectedTable" width="381" height="21"> 
    </mx:ComboBox> 
</mx:HBox> 
<mx:DataGrid id="dg1" width="452"> 
</mx:DataGrid> 
</mx:VBox> 
</mx:Application>

When I compile and run this in Flex Builder 2, I see the result shown in Figure 1.

figure 1
Figure 1. The interface layout

Pretty slick, huh? Nice gradient background. And the controls certainly look pretty. And that's with a minimum of work on my part. Of course, a graphic designer could really polish this up with some flourishes, like transitional effects and images. As for me, I'll just stick with a nice clean interface and rest assured that it will look and work the same on every client, which isn't an easy feat in Ajax applications.

Getting the Database List

The first step in fleshing out the code of this Flex application is to populate the combo box of databases at the top of the form after the application loads. The code for this is shown in Listing 3.

Listing 3. flexmysql2.mxml

<?xml version="1.0" encoding="utf-8"?> 
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" 
initialize="onInitialize()"> 
<mx:Script> 
<![CDATA[ 
import mx.collections.ArrayCollection; 

private static const SERVICE_BASE:String = "http://localhost/sql/req.php"; 

public function onInitialize():void 
{ 
    myservice.url = SERVICE_BASE; 
    myservice.send( null ); 
} 
public function onResult(event:Event):void 
{ 
    selectedDatabase.dataProvider = myservice.lastResult..database.*; 
} 
]]> 
</mx:Script> 
<mx:HTTPService id="myservice" result="onResult(event)" resultFormat="e4x"> 
</mx:HTTPService> 
<mx:VBox horizontalAlign="left"> 
<mx:HBox> 
    <mx:Label text="Database:" /> 
    <mx:ComboBox id="selectedDatabase" width="381" height="21"> 
    </mx:ComboBox> 
</mx:HBox> 
<mx:HBox> 
    <mx:Label text="Table:" /> 
    <mx:ComboBox id="selectedTable" width="381" height="21"> 
    </mx:ComboBox> 
</mx:HBox> 
<mx:DataGrid id="dg1" width="452"> 
</mx:DataGrid> 
</mx:VBox> 
</mx:Application>

I've left out some of the code that's the same as the previous example to reduce the size of the code fragment. The big addition is the <mx:HTTPService> tag, which creates an HTTP Service Flex object that will communicate with the server. This service object is called by the onInitialize method, which is called when the Flex application starts up.

The onInitialize method sets the URL for the service, and then starts the request. When the request completes, the onResult method is called. That onResult method sets the dataProvider on the combo box to the results returned from the web server.

What's really, really cool about this is this section:

myservice.lastResult..database.*

Have a look at that. If you haven't seen E4X in action before, let me tell you a little about it. The myservice.lastResult variable is actually an XML document. The '..' syntax is the equivalent of the XPath '//' syntax. It means "give me any tag of this name;" in this case, "give me any database tag." The star means any child of the database tag. And because there is only one child of the database tag, the literal text of the name of the database, this code means "give me the names of each of the databases from the XML document." How cool is that?

This E4X extension to ActionScript is why people rave about how easy it is for Flex to talk to XML data sources. No other language I know of makes it as easy to query XML documents.

So with my E4X rant out of the way, I can run this application in my browser and see the result shown in Figure 2.

figure 2
Figure 2. The database combo box filled with the database names

When I click on the combo, it drops down and you can see the list of databases I have on my machine. Yeah, I have a lot. Almost every article about PHP, Flex, Rails, or whatever uses a database. So, I have lots and lots of databases.

Building the Table Viewing Code

The final step in building this example Flex application is to handle populating the tables' drop-down menus and showing the data from the selected table in the data grid. The complete code for the example is shown in Listing 4.

Listing 4. flexmysql.mxml

<?xml version="1.0" encoding="utf-8"?> 
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="vertical" 
initialize="onInitialize()"> 
<mx:Script> 
<![CDATA[ 
import mx.collections.ArrayCollection; 

private static const SERVICE_BASE:String = "http://localhost/sql/req.php"; 

private var loadingDatabases:Boolean = true; 
private var loadingTables:Boolean = false; 

public function onInitialize():void 
{ 
    loadingDatabases = true; 
    myservice.url = SERVICE_BASE; 
    myservice.send( null ); 
} 
public function onResult(event:Event):void 
{ 
    if ( loadingDatabases ) 
    { 
        loadingDatabases = false; 
        selectedDatabase.dataProvider = myservice.lastResult..database.*; 
        onSelectDatabase(); 
    } 
    else if ( loadingTables ) 
    { 
        loadingTables = false; 
        var tables:Array = new Array(); 
        for each ( var tablRecord:XML in myservice.lastResult..record ) 
        { 
            for each( var tablCol:XML in tablRecord.* ) 
                tables.push( tablCol..*.toString() ); 
        } 
        selectedTable.dataProvider = tables; 

        onSelectTable(); 
    } 
    else 
    { 
        var records:Array = new Array(); 
        for each ( var record:XML in myservice.lastResult..record ) 
        { 
            var outRecord:Array = new Array(); 
            for each( var column:XML in record.* ) 
                outRecord[ column.name() ] = column..*.toString(); 
            records.push( outRecord ); 
        } 
        var data:ArrayCollection = new ArrayCollection( records ); 
        dg1.dataProvider = data; 
    } 
} 
public function onSelectDatabase():void 
{ 
    loadingDatabases = false; 
    loadingTables = true; 

    var url:String = SERVICE_BASE; 
    url += "?mode=getTables&db="+selectedDatabase.selectedLabel; 
    myservice.url = url; 
    myservice.send(null); 

} 
public function onSelectTable():void
{ 

    var url:String = SERVICE_BASE; 
    url += "?mode=getData&db="+selectedDatabase.selectedLabel; 
    url += "&table="+selectedTable.selectedLabel; 
    myservice.url = url; 
    myservice.send(null); 
} 
]]> 
</mx:Script> 
<mx:HTTPService id="myservice" result="onResult(event)" resultFormat="e4x"> 
</mx:HTTPService> 
<mx:VBox horizontalAlign="left"> 
<mx:HBox> 
    <mx:Label text="Database:" /> 
    <mx:ComboBox id="selectedDatabase" width="381" height="21" 
        change="onSelectDatabase()"> 
    </mx:ComboBox> 
</mx:HBox> 
<mx:HBox> 
    <mx:Label text="Table:" /> 
    <mx:ComboBox id="selectedTable" width="381" height="21" 
        change="onSelectTable()"> 
    </mx:ComboBox> 
</mx:HBox> 
<mx:DataGrid id="dg1" width="452"> 
</mx:DataGrid> 
</mx:VBox> 
</mx:Application>

The big change here is the addition of the code in onSelectDatabase() to get the list of tables, then to populate it in onResult(), and the addition of the onSelectTable(), which retrieves the data from the table and sets the data grid in the onResult() handler.

When I run this example in my browser, I see something like Figure 3.

figure 3
Figure 3. After running the 'show tables' query

In this case, I use the nasa database that I built for a lecture I gave at NASA Ames (yes, I used the "rocket science" joke, because I was talking to a bunch of rocket scientists). The database contains a single table called mission, which includes the name and year of each of the unmanned missions that NASA has launched.

I can sort by name by just clicking on the name column in the data grid, as shown in Figure 4.

figure 4
Figure 4. Showing the data from the mission table

Then, I can sort by year by just clicking on the year column, as you can see in Figure 5.

figure 5
Figure 5. The disco ball query

Looks like we haven't launched a whole lot of missions in the last couple of years.

Next Steps

From here, there are a lot of places I could take this example. On the front end, in Flex, I could add some more user-friendly controls to add, remove, or update tables. I could even go as far as providing data filtering or editing functionality that would change the data in the tables. The data grid control is powerful enough to handle in-place editing. Or I could take it in a completely different direction and use the Flex charting package to do some data analysis and visualization.

Conclusion

I see a lot of potential for the combination of PHP and Flex. PHP makes it easy to deliver web services on the back end, either exclusively or in combination with an HTML front end. I'd love to see PHPMyAdmin extended with a web services interface that would let anyone write front ends in Flex, Ajax, Silverlight, desktop widgets, or whatever.

Flex provides a solid front-end tool for PHP developers. It gets around the client-side incompatibility issues that plague Ajax developers. With Flex 2, it's not tied to a specific server technology, so after the Flex application is compiled, it can be deployed anywhere, because it's just a SWF file. And as you can see, with easy web service objects and the powerful E4X language extension, it's very easy to have Flex applications talk to almost anything on the back end.

I hope you give it a try, and if you do some fun stuff with PHP and Flex, let me know and perhaps we can write an article about it together.

Resources

Jack Herrington is an engineer, author and presenter who lives and works in the Bay Area. His mission is to expose his fellow engineers to new technologies. That covers a broad spectrum, from demonstrating programs that write other programs in the book Code Generation in Action. Providing techniques for building customer centered web sites in PHP Hacks. All the way writing a how-to on audio blogging called Podcasting Hacks.


Return to ONLamp.

Copyright © 2009 O'Reilly Media, Inc.