PHP5 quick database E4X queries

The following code has been changed to protect the innocent (in this case, my server). All code is valid and is currently used in several deployed applications. Extra spacing added for easier viewing in Adobe's cookbook.

PHP:

<?php
/**
 * Quick PHP MySQL E4X Query Tool
 * @author James Zimmerman II
 */
// MySQLi database pulled in.
$dbConnector = new mysqli(
  'my.database.com',
  'myUser',
  'myPass',
  'myDatabase'
);
// Dirty to Clean arrays for key names
$dirty = array( "(", ")" );
$clean = array( "" );
// Use a DOMDocument object for strict output format.
$output = new DOMDocument( '1.0', 'UTF-8' );
// We'll need a root element container.
$root = $output->appendChild(
  $output->createElement( 'result' )
);
// Read our query from POST.
$sql = $_POST['query'];
// Perform our query.
$dbResult = $dbConnector->query( $sql );
// For inserts we want to pull the inserts resulting
// ID as our result.
if( strtolower( substr( $sql, 0, 6 ) ) == "insert" ) {
  $dbResult = $dbConnector->query(
    "SELECT LAST_INSERT_ID() AS ID"
  );
}
// Loop our results and create the XML
if( $dbResult->num_rows < 1 ) {
  // Provide default for no result
  $resultMessage = $root->appendChild(
    $output->createElement( 'row' )
  );
  $resultMessage->setAttributeNode(
    new DOMAttr( "record", "0" )
  );
  $resultNotice = $resultMessage->appendChild(
    $output->createTextNode( "No valid results" )
  );
} else {
  // Process our result to XML output.
  for( $i=1; $i<=$dbResult->num_rows; $i++ ) {
    // Pull an associative array.
    $resultRow = $dbResult->fetch_assoc();
    // Create our row.
    $returnedData = $root->appendChild(
      $output->createElement( 'row' )
    );
    // Mark it with a particular ID.
    $returnedData->setAttributeNode(
      new DOMAttr( "record", $i )
    );
    // Process the row's data.
    foreach( $resultRow as $key=>$value ) {
      // Create a data chunk.
      $returnedData->setAttributeNode(
        new DOMAttr(
          // Attribute
          str_replace( $dirty, $clean, $key ),
          // Value
          $value
        )
      );
    } //foreach
  } //for
} //else
// Close our database connection.
$dbConnector->close();
// Echo our generated output.
echo $output->saveXML();
?>

Using MySQLi as a database connector, although in this simplified case mysql would be just as valid, I allow the Flex application to directly post it's query to the database. Since we have control over the query in the Flex application, we also know what fields to expect unless we're doing * queries so it's a pretty straight forward arrangement. I use DOMDocument to generate a properly formatted XML document for my results to be returned to Flex. This code snippet is actually one in a collection of five that are all geared to provide simple and generic data interfaces for my daily services. With a simple change to the XML creation loop, large data could be placed in subordinate elements and extremely large data further encapsulated in CDATA blocks. I often use my XML results in both Flex, PHP and Ajax interfaces so these files have proven quite handy for those purposes.

For the Flex pieces, here is a simplified example (not from working copy):

MXML:

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" creationComplete="submitQuery()">
  <mx:Script>
    <![CDATA[
      private function submitQuery():void {
        var queryString:String =
          "SELECT id, name, email FROM myTable";
        DBService.send( { query: queryString } );
      }
    ]]>
  </mx:Script>
  <mx:HTTPService id="DBService" url="dbService.php"
    resultFormat="e4x" method="post"/>
  <mx:DataGrid left="0" right="0" top="0" bottom="0" dataProvider="{DBService.lastResult.row}">
    <mx:columns>
      <mx:DataGridColumn headerText="ID" dataField="@id"/>
      <mx:DataGridColumn headerText="Name" dataField="@name"/>
      <mx:DataGridColumn headerText="Email" dataField="@email"/>
    </mx:columns>
  </mx:DataGrid>
</mx:Application>

Additional options could easily be added to mutate the query as necessary to add WHERE attachments, GROUP BY and ORDER BY segments as necessary. It's a powerful but relatively simple base to build from and can easily be customize for particular needs. You could even take the Flex to another step and concoct a method for generating the DataGridColumn objects on the fly based on the provided attributes though I haven't built such a system at this time.

NOTE: additional security risks are not addressed in this example, it is highly recommended to include some authentication before allowing direct XML results from your data.