Tojio Lab

As the DataGrid class in Flex is the perfect tool for managing table data, we also need an efficient and reliable way to store these data. The example here uses an HTTPService to retrieve the data from a remote webserver and to send update packages back to it.

This Tutorial uses an example user management tool for a community site. Users can be (de-)activated, their credits and status within the system can be changed. Any changes in the DataGrid lead to an instant update of the relational database in which the users are stored.

Task #1: retrieving and transforming data with PHP
In order to populate a DataGrid with data from a mysql database, we first have to get a suitable representation. A Flex DataProvider typically reads this sort of “flat” XML files consisting only of nodes representing table rows, e.g:

<dataset>

  <entry>

    <id>2</id>

    <login_name>hai_kai</login_name>

    <last_login>2007-11-05 09:47:54</last_login>

    <credits>8</credits>

    <status>4</status>

    <active>true</active>

  </entry>

  <entry>

    <id>4</id>

    <login_name>Meerblick</login_name>

    <last_login>2007-11-06 09:54:25</last_login>

    <credits>14</credits>

    <status>1</status>

    <active>true</active>

  </entry>

    .

    .

    .

</dataset>

So this structure is pretty similar to a table (be it a table in a relational DB or the DataGrid) and easily created in PHP after we’ve got a result set by querying our relational database. The example DB used here holds a user table that is retrieved by a PHP script like this (using XML-functions of PHP5):

define('DB_TYPE', 'mysql');
define('DB_HOST', 'localhost');
define('DB_NAME', 'name_of_your_database');

// constants for accessing the DB
define('DB_USER', 'username_for_the_DB');
define('DB_PASS', 'your_DB_password');

// create a new dom structure that will
// be sent as XML result
$output 	= new DOMDocument();
$root_element 	= $output->createElement('dataset');
$output->formatOutput 	= true;
$output->appendChild($root_element);

// connect to mysql-Server and select our user DB
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS)
	or die('Could not connect: ' . mysql_error());

mysql_select_db(DB_NAME) or die('Could not select database');

// prepare the query
$query = "SELECT `id`, login_name`, `last_login`,
		`credits`, `status`, `active`
          FROM 	 `user`";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// for each entry in the result set...
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
        // create an entry-node and append it to the root element
	$entry = $output->createElement('entry');
	$root_element->appendChild($entry);

        // for each field in the entry
	foreach (array_keys($line) as $key)
	{
                // append a node with the field's name
                // and add the field's value as textnode
		$node = $output->createElement($key);

		// we have to convert mySQL booleans (0/1)
		// to explicit true/false- values for Flex
		if ($key == 'active')
		{
			$boo = ($line[$key]) ? "true" : "false";
			$content = $output->createTextNode($boo);
		}
		else $content = $output->createTextNode($line[$key]);

		$node->appendChild($content);
		$entry->appendChild($node);
		$root_element->appendChild($entry);
	}
}

// Free resultset, close connection
mysql_free_result($result);
mysql_close($link);

// return the xml
echo $output->saveXML();

So that was the server-side data handling… to populate your DataGrid with this user data, you just have to define a DataProvider for it that reads the url by means of an HTTPService when the application is initialized. In your init code, invoke the send()-Method of the service.

// define bindable ArrayCollection
[Bindable]
protected var userCollection:ArrayCollection =
			new ArrayCollection();

// define a HTTPService that requests the XML data
<mx:httpservice>
    id="userlistrequest"
    url="http://yourserver.com/yourdbscript.php"
    useProxy="false"
    result="userCollection=ArrayCollection(userlistrequest.lastResult.dataset.entry)"
/>
</mx:httpservice>

The second part of this Tutorial will cover some Flex internal stuff like custom cell rendering / item editors and, of course, some of the nasty little problems that can (or will?) arise with this altogether…. The Third part finally shows how to send updated data instantly back to the DB…