ONLamp.com
oreilly.comSafari Books Online.Conferences.

advertisement


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.

Pages: 1, 2, 3

Next Pagearrow





Sponsored by: