Data Overload - XML

The previous example showed you how to store the information provided by the user in a local file on the client. Though this seems interesting at first glance, it isn't very useful in real life (when was the last time you wanted to do this?) Most often, you would want the data to be sent to the server, safe and secure in a database or other storage engine. How does XForms stand up to this challenge? Pretty well, actually – and it even adds some interesting attributes to control the data being submitted.

In order to demonstrate, I'll revise the previous example to submit the user data to a server-side script, which takes care of adding it to a MySQL database. In this example, the database is called "db1", the table is called "immigrants", and the SQL code to create the table looks like this:


CREATE TABLE `immigrants` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL default '',
  `citizenship` varchar(50) NOT NULL default '',
  `purpose` char(1) NOT NULL default '',
  `immunization` varchar(50) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
   PRIMARY KEY  (`id`)
)



As you can see, this is pretty straightforward stuff, with each field of the table mapping to a node in the XML file created in the previous example.

Next up, altering the XForm model to point to a server-side PHP script instead of a local file:


<!-- form model -->
<xforms:model id="immigration">
 <xforms:instance src="immigration.xml" />
 <xforms:submission id="submit" 
  action="/scripts/register.php"
  method="post" />
</xforms:model>



Notice that no change is needed to the form input controls, or any other section of the XForm. This is an example of the separation between form and function that XForms promises.

Finally, here's the PHP script that takes the submitted form data and converts it into an INSERT query:


<?php
// initialize some variables;
$currentTag = "";
$values = array();
$allowedFields = array("name", 
"citizenship", "purpose", 
"immunization", "address");
// database parameters
$host = "localhost";
$usr = "john";
$pwd = "doe";
$db = "db1";
// handlers
function startElementHandler($parser, 
$name, $attributes) 
{
 global $currentTag;
 $currentTag = $name;
}
function endElementHandler($parser, 
$name) 
{
 global $values, $currentTag; 
 global $connection, $table;
 
 if(strtolower($name) == "immigrant") 
 {
  // generate SQL
  $query = "INSERT INTO immigrants";
  $query .= "(name, citizenship, purpose, 
                  immunization, address)";
  $query .= "VALUES("" . join("","",
                            $values) . "");";
    
  // uncomment for debug purposes
  // print $query;      
  
  // execute query
  $result = mysql_query($query) or 
               die("Error in query: $query. " .
               mysql_error());
  
  // reset variables
  $values = array();
  $currentTag = "";
 }
}
function characterDataHandler($parser, $data) 
{
 global $currentTag, $values, $allowedFields;
 
 $currentTag = strtolower($currentTag);
 
 if(in_array($currentTag, 
 $allowedFields) && trim($data) != "") 
 {
  $values[$currentTag] = 
  mysql_escape_string($data);
 }
 
}
$parser = xml_parser_create();
// get the XML data
$data = $HTTP_RAW_POST_DATA;
// set SAX parser options
xml_parser_set_option($parser,
XML_OPTION_CASE_FOLDING,0);

 
xml_parser_set_option($parser,
XML_OPTION_SKIP_WHITE,1);
// set element handlers
xml_set_element_handler($parser, 
"startElementHandler", "endElementHandler");
xml_set_character_data_handler($parser, 
"characterDataHandler");
// connect to database
$conn = mysql_connect($host, $usr, $pwd) or 
            die("Unable to connect to the 
            database");
mysql_select_db($db) or 
die("Unable to select database");
// parse XML
if (!xml_parse($parser, $data )) 
{
    die(sprintf("XML error: %s at line %d",
    xml_error_string(xml_get_error_code($parser)),
    xml_get_current_line_number($parser)));
}
// clean up
xml_parser_free($parser);
mysql_close();


Now try it out and see for yourself: enter some data into the form, submit it and then check the database to see if your values were inserted correctly. Here's what you might see:

[output]
mysql> SELECT * FROM immigrants;

1 row in set (0.00 sec)
[/output]

How did this happen? Well, unlike traditional forms, which submit data using name-value pairs, XForms submits data as a well-formed XML document. This document can then be parsed using either a DOM or SAX parser, or even transferred directly to any other application that understands XML.

PHP comes with a built-in SAX parser, which is what I've used in the example above to parse the XML document. SAX, or the Simple API for XML, is one of the most common methods of parsing an XML document. Essentially, a SAX parser reads the XML document sequentially, triggering specific user-defined functions when it finds an opening tag, character data, closing tag, CDATA block and so on. In the example above, these user-defined functions are called startElementHandler(), endElementHandler() and characterDataHandler().


$parser = xml_parser_create();
xml_set_element_handler($parser, 
"startElementHandler", "endElementHandler");
xml_set_character_data_handler($parser, 
"characterDataHandler");



Of these, the major work in the script above is done by the characterDataHandler() function. This reads the various values entered by the user from the XML document tree and builds the SQL query after using the mysql_escape_string() function to make the values ready for insertion in the database.


function characterDataHandler($parser, $data) 
{
 global $currentTag, $values, $allowedFields;
 
 $currentTag = strtolower($currentTag);
 
 if(in_array($currentTag, $allowedFields) && trim($data) != "") 
 {
  $values[$currentTag] = mysql_escape_string($data);
 }
 
}



The script above won't make much sense to you unless you've played a little with SAX. In case you haven't, drop by http://www.melonfire.com/community/columns/trog/article.php?id=71, find out what you missed, and afterwards come back here and review the script again. You can also read more about SAX at http://www.saxproject.org/ and http://www.xmlphp.com/

You can also parse the XML document submitted by the XForm using the DOM; I leave that to you as an exercise.