jump to navigation

Using Flex, PHP, and JSON to Modify a MySQL Database August 28, 2010

Posted by Tournas Dimitrios in Flex and PHP.
trackback

To follow-up on the Flex and PHP tutorial we did earlier I thought it would be nice to do a complete tutorial on php, mysql, flex, and json. This tutorial should show a decent way to send data in a database to and from flex to php using json. I will explain this in more detail in just a moment.

First lets take a look at what will be the result of this tutorial and see what it does.  This is a simple flex app with a datagrid component and a button. The datagrid is filled with some sample data, which, in this case, is some information about a few of the tutorials on this Blog. The info includes the title, the author, and a rating for the tutorial, and all of this information is pulled from a database on the webserver when the app loads.

The app allows you to change the rating for each tutorial, but does not allow you to change the other columns. You can click the “Update Database” button, and the ratings you entered will be sent to the webserver using json, and stored in a database.

So how is all of this done? Well lets start by taking a thousand foot view of how the communication between the application and the webserver will work. On the top of the diagram below we have the process of getting the data from the database to the flex application. As you can see we start over at the client block, where it sends a message to the php page on the webserver requesting data. The php in turn sends a query to the database and get the results (or error) back from MySql. This information is then encoded in json and sent back to the client. The flow at the bottom of the diagram describes how data from the flex app is pushed back up into the database. The client encodes its data in json, and proceeds to send it to the php page on the webserver. The php page takes this data, decodes it into php objects, and updates the database. the php page then sends back a success or failure message to the client.

Now lets get going into some code. First things first we need to make sure we have a database, mine is called ‘testDB’. I am not going to go over creating the database itself in MySql but if you need help with this MySQL’s site has some great reference material, including tutorials and examples. Once you have the database setup we want to add a table to it. In MySQL you can run the command below to get the table we are going to use in this tutorial. We are using a very simple table that just holds an id, title, author, and rating for our tutorials.

DROP TABLE IF EXISTS `testDb`.`Tutorials`;
CREATE TABLE  `testDb`.`Tutorials` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `author` varchar(255) NOT NULL,
  `rating` double NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The next step we are going to do is create our php file that will sit between the database and flex app. This file is actually a fairly simple script. We start off by creating a few variables that we use for our database connection: the database name, username, password, and address of the server. The values for those variables in the script below are just placeholders – you should fill them in with the values correct for your setup. We also setup two cases in our script which are executed depending on the “GET” parameters we receive from our client. One is for getting the data from the database, and one is for updating the data. And so we start with the following php code:

< ? p h p
$USERNAME = 'yourusername';   //database username
$PASSWORD = 'yourpassword';   //database password
$DATABASE = 'testDb';         //database name
$URL = 'localhost';           //database location

if(isset($_GET['getTutorials']))
{
}
elseif(isset($_GET['setTutorials']))

{
}
?>

Next we fill out our getTutorials if statement. The first thing we want to do is connect to the database. These functions are part of the base php install. We then build our query to get all the tutorials from our table and create an array for our results. We can now run our query and get our results back. You could check for errors at this point, but I do not (in order to make the code simpler to read). We then take each row from our results using mysql_fetch_assoc statement and add each of them to the $returnArray. Once we have added all the rows we then close our connection to the database. Finally, we encode the $returnArray into JSON and send it on to the client.

mysql_connect($URL, $USERNAME, $PASSWORD);
mysql_select_db($DATABASE) or die('Cannot connect to database.');

$returnArray = array();

$query = 'SELECT * FROM Tutorials';
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
  array_push($returnArray, $row);
}

mysql_close();
echo json_encode($returnArray);

Now we have to fill in the second if block. We start a little different in this one because here we are getting data back from our client. The data we are getting back is through a “GET” parameter so the first thing I do is use urldecode on the data to change any %– (where — is a hex number) back to the actual symbol. Now this string should be the JSON encoded data sent from the client, but for some reason the data sent by our client has a few extra slashes in it, so I just get rid of those with a str_replace and we are ready to decode the info.

We can now decode using the function json_decode. You may notice that we pass two parameters to the function, the first is our data and the second tells the function that we want the data back as an associative array (which makes our job easier later). Now we open up a connection to our database just like before. Next we loop through the array we got back from json_decode, running an update query on each entry. We create the query this time using sprintf to insert our variables from our array of data, and we use mysql_real_escape_string to properly escape any data in the query. The result of an “Update” statement in php is “true” if it is successful and “false” if not, so we check to make sure we successfully update the database, and if not we close the connection and return the error to our client. The last thing we do is if all the data updates successfully we close the database connection and return a “database updated” message back to the client. And here is how it all looks:

$jsonString = urldecode($_GET['jsonSendData']);
$jsonString = str_replace("\\", "", $jsonString);
$data = json_decode($jsonString, true);

mysql_connect($URL, $USERNAME, $PASSWORD);
mysql_select_db($DATABASE) or die('Cannot connect to database.');

foreach ($data as $tutorialEntry) {
  $query = sprintf(
    'UPDATE Tutorials SET rating = "%s" WHERE id = "%s"',
    mysql_real_escape_string($tutorialEntry['rating']),
    mysql_real_escape_string($tutorialEntry['id']));

  $result = mysql_query($query);

  if(!$result)
  {
    mysql_close();
    echo mysql_error();
    return;
  }
}
mysql_close();
echo "database updated";

And all that php code together looks like this:

< ? p h p
$USERNAME = 'yourusername';   //database username
$PASSWORD = 'yourpassword';    //database password
$DATABASE = 'testDb';   //database name
$URL = 'localhost';        //database location

if(isset($_GET['getTutorials']))
{
  mysql_connect($URL, $USERNAME, $PASSWORD);
  mysql_select_db($DATABASE) or die('Cannot connect to database.');

  $returnArray = array();

  $query = 'SELECT * FROM Tutorials';
  $result = mysql_query($query);

  while($row = mysql_fetch_assoc($result))
  {
    array_push($returnArray, $row);
  }

  mysql_close();
  echo json_encode($returnArray);
}
elseif(isset($_GET['setTutorials']))
{
  $jsonString = urldecode($_GET['jsonSendData']);
  $jsonString = str_replace("\\", "", $jsonString);
  $data = json_decode($jsonString, true);

  mysql_connect($URL, $USERNAME, $PASSWORD);
  mysql_select_db($DATABASE) or die('Cannot connect to database.');

  foreach ($data as $tutorialEntry) {
    $query = sprintf(
      'UPDATE Tutorials SET rating = "%s" WHERE id = "%s"',
      mysql_real_escape_string($tutorialEntry['rating']),
      mysql_real_escape_string($tutorialEntry['id']));

    $result = mysql_query($query);
     
    if(!$result)
    {
      mysql_close();
      echo mysql_error();
      return;
    }
  }
  mysql_close();
  echo "database updated";
}
?>

On to the Flex now. We have our backend ready to go, so next up is building the client. We start off by building a simple interface in flex, nothing spectacular. We add a panel, datagrid to hold our data, a button to send updated data to php, and a label to put status updates in. We set some specific widths on our datagridcolumn tags just to make our data look a little nicer. This should produce some code that looks like the following:

< ? x m l version="1.0" encoding="utf-8"?>
<m x: Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute"
   width="535" height="345">
  <m x: Panel x="0" y="0" width="535" height="345" layout="absolute"
     title="Flex, PHP, Mysql - Updating through JSON">
    <m x: DataGrid id="dgData" x="10" y="10" width="495" height="241">
      <m x: columns>
        <m x: DataGridColumn headerText="Name" dataField="name"/>
        <m x: DataGridColumn headerText="Author" dataField="author" width="115"/>
        <m x: DataGridColumn headerText="Rating" dataField="rating" width="50"/>
      </m x: columns>
    </m x: DataGrid>
    <m x: Button x="10" y="259" label="UpdateDatabase" id="butUpdate"/>
    <m x: Label x="140" y="261" id="lblStatus"/>
  </m x: Panel>
</m x: Application>

Now we can work on getting data from php and into our datagrid. The first thing we do is create an HTTPService to go out to our php page and get the data. We specify a couple attributes in our <mx:HTTPService> tag to define our service. We give it an id to reference it by and set the url property to the url of the php page. Also we say we don’t want to use a proxy, the method we are going to use is “GET”, and we want the result back as text. The final parameter that we set is result , and here we set the function we want called when the request returns – in this case, getPHPData. We have not yet defined this function, but don’t worry, we will talk about it in just a few moments. After the opening tag for the HTTPService is completed, we create a request tag, where we specify that we want the GET parameter “getTutorials” to have a value of true in the request. And this code will go right below the opening application tag.

<m x: HTTPService id="getData" url="/files/phpdb_json_tutorial.php"
   useProxy="false" method="GET" resultFormat="text" result="getPHPData(event)">
  <m x: request xmlns="">
    <getTutorials>"true"</getTutorials>
  </m x: request>
</m x: HTTPService>

Next we setup the functions that will trigger the getData request and the function that will handle the result of that request (getPHPData). So in a <mx:Script> tag we create two functions, one is called “initDataGrid” and other is “getPHPData”. At this point we also add a [Bindable] ArrayCollection to hold the data. Now in our “initDataGrid” function we initialize our ArrayCollection to an empty new one and also we tell our HTTPService trigger the request for data. Once we get the data back from php we will handle it in the “getPHPData” function. This will take the raw data from the result event and decode it using JSON.decode. The decoded data is saved as an Array and then we create an ArrayCollection from this array and set our “dataArray” variable equal to this. All of this goes in right after the opening application tag.

<m x: Script>
 <![CDATA[
   import mx.rpc.events.ResultEvent;
   import mx.collections.ArrayCollection;
   import com.adobe.serialization.json.JSON;
   
   [Bindable]
   private var dataArray:ArrayCollection;
   
   private function initDataGrid():void
   {
     dataArray = new ArrayCollection();
     getData.send();
   }
   
   private function getPHPData(event:ResultEvent):void
   {
     var rawArray:Array;
     var rawData:String = String(event.result);
     rawArray = JSON.decode(rawData) as Array;
     dataArray = new ArrayCollection(rawArray);
   }
 ]]>
</m x: Script>

The data still isn’t showing up in our DataGrid, but this is because we haven’t hooked up our “initDataGrid” function and bound our “dataArray” yet. This is a simple task – all we do is update our DataGrid tag with a dataProvider property and creationComplete event which calls our initialize function. Our new tag look like this:

<m x: DataGrid id="dgData" x="10" y="10" width="495" height="241"
   dataProvider="{dataArray}" creationComplete="{initDataGrid()}" >

We now have data from the MySQL database showing up in the datagrid. Lets now work on updating our data in our flex client and getting it back to the database. The first thing we want to do is make our datagrid editable, and this is just a property we set in the tag. But because we only want the rating column to be editable, we add an editable tag to each column, which we set to false for the title and author and true for the rating. The new datagrid component code is below:

<m x: DataGrid id="dgData" x="10" y="10" width="495" height="241" editable="true"
 dataProvider="{dataArray}" creationComplete="{initDataGrid()}">
  <m x: columns>
    <m x : DataGridColumn headerText="Name" dataField="name" editable="false"/>
    <m x: DataGridColumn headerText="Author" dataField="author" width="115"
       editable="false"/>
    <m x: DataGridColumn headerText="Rating" dataField="rating" width="50"
       editable="true" />
  </m x: columns>
</m x: DataGrid>

Now we can update our datagrid but the changed data isn’t actually getting back into our ArrayCollection – for this we add a <mx:Binding> tag just before our opening panel tag. We set the source of the binding to the data provider of our datagrid and the destination as our “dataArray” variable. This will make sure that as the datagrid gets updated the ArrayCollection is updated as well.

<m x: Binding source="dgData.dataProvider as ArrayCollection" destination="dataArray"/>

Now it’s time to get this updated data back to the database. In order to do this we need another HTTPService, and we set this one up exactly like before, except this one has a different id and a different function is called when the results are returned. We also do NOT send a “getTutorials” parameter. So this new HTTPService tag looks like the following and goes right below our other service tag.

<m x: HTTPService id="sendData" url="../files/phpdb_json_tutorial.php"
   useProxy="false" method="GET" resultFormat="text"
   result="updatedPHPDataResult(event)">
</m x: HTTPService>

We also update our button at this point to call a function when clicked, which in this case will be sendPHPData.

<m x: Button x="10" y="259" label="UpdateDatabase" id="butUpdate" click="{sendPHPData()}"/>

Now we need to create the function referred to by the previous two blocks of code. First, lets work on sendPHPData. This function will be called when the “Update Database” button is clicked, and will take the data from the datagrid, encode it as json, and push it back up to the database. We first declare an object which we use for our url parameters (name/value pairs). Next we actually encode the data of our ArrayCollection, note we use the getArray function to get the data as an Array out of the collection. Once we have encoded the data we pass it through a helper function, escape. Basically this function checks for certain symbols and replaces them with the url hex codes, making the data url safe. Now we set our parameters we want to pass, first being “setTutorials” which will let our php know we want to update the database, and second the JSON data itself. All that is left is to send the request out with our parameter object. Below you will see the “sendPHPData” function, this goes in our tag.

private function sendPHPData():void
{
  var objSend:Object = new Object();
  var dataString:String = JSON.encode(dataArray.toArray());
  dataString = escape(dataString);
  objSend.setTutorials = "true";
  objSend.jsonSendData = dataString;
  sendData.send(objSend);
}

The second function is updatedPHPDataResult, which is called when the sendData request returns. All it does is display the result of the request on the screen. We do this by setting the text of our status label to the result we get back from php. Again this function goes in our script tag.

private function updatedPHPDataResult(event:ResultEvent):void
{
  lblStatus.text = String(event.result);
}

We now have a working client that can update the database. You can stop here and everything should work. But its always nice to do a little error checking, so lets see about checking the rating being entered before we let the edit complete. This can be done by using an event in the DataGrid tag, itemEditEnd. This event is fired at the end of the edit but before the value is put into the data provider. So I changed the DataGrid tag to the following:

<m x: DataGrid id="dgData" x="10" y="10" width="495" height="241"
 dataProvider="{dataArray}" creationComplete="{initDataGrid()}"
 editable="true" itemEditEnd="{checkRating(event)}">

And also I added the function defined by the event to the script tag in our mxml. This function does the following: It grabs the value that is being changed (the new value) and checks to make sure it is a number between 0 and 10. If it isn’t I show an error message in the status label. And by calling the function preventDefault on the edit event I keep the edit box open restoring the original value. I added this just to make sure no ugly data gets pushed back to the database. So the function looks like:

private function checkRating(event:DataGridEvent):void
{
  var txtIn:TextInput = TextInput(event.currentTarget.itemEditorInstance);
  var curValue:Number = Number(txtIn.text);
  if(isNaN(curValue) || curValue < 0 || curValue > 10)
  {
    event.preventDefault();
    lblStatus.text = "Please enter a number rating between 0 and 10";
  }
}

We are now left with the following code in our mxml file. This takes care of our client.

< ? x m l version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute"
   width="535" height="345">
  <m x: Script>
   <![CDATA[
     import mx.events.DataGridEvent;
     import mx.controls.TextInput;
     import mx.rpc.events.ResultEvent;
     import mx.collections.ArrayCollection;
     import com.adobe.serialization.json.JSON;
   
     [Bindable]
     private var dataArray:ArrayCollection;
   
     private function initDataGrid():void
     {
       dataArray = new ArrayCollection();
       getData.send();
     }
   
     private function getPHPData(event:ResultEvent):void
     {
       var rawArray:Array;
       var rawData:String = String(event.result);
       rawArray = JSON.decode(rawData) as Array;
       dataArray = new ArrayCollection(rawArray);
     }
 
     private function sendPHPData():void
     {
       var objSend:Object = new Object();
       var dataString:String = JSON.encode(dataArray.toArray());
       dataString = escape(dataString);
       objSend.setTutorials = "true";
       objSend.jsonSendData = dataString;
       sendData.send(objSend);
     }
   
     private function updatedPHPDataResult(event:ResultEvent):void
     {
       lblStatus.text = String(event.result);
     }
   
     private function checkRating(event:DataGridEvent):void
     {
       var txtIn:TextInput = TextInput(event.currentTarget.itemEditorInstance);
       var curValue:Number = Number(txtIn.text);
       if(isNaN(curValue) || curValue < 0 || curValue > 10)
       {
         event.preventDefault();
         lblStatus.text = "Please enter a number rating between 0 and 10";
       }
     }
   ]]>
 </m x: Script>
  <m x: HTTPService id="getData" url="/files/phpdb_json_tutorial.php"
     useProxy="false" method="GET" resultFormat="text"
     result="getPHPData(event)">
    <m x: request xmlns="">
      <getTutorials>"true"</getTutorials>
    </m x: request>
  </m x: HTTPService>
  <m x: HTTPService id="sendData" url="/files/phpdb_json_tutorial.php"
     useProxy="false" method="GET" resultFormat="text"
     result="updatedPHPDataResult(event)">
  </m x: HTTPService>
  <m x: Binding source="dgData.dataProvider as ArrayCollection"
     destination="dataArray"/>
  <m x: Panel x="0" y="0" width="535" height="345" layout="absolute"
     title="Flex, PHP, Mysql - Updating through JSON">
    <m x: DataGrid id="dgData" x="10" y="10" width="495" height="241"
       dataProvider="{dataArray}" creationComplete="{initDataGrid()}"
       editable="true" itemEditEnd="{checkRating(event)}">
      <m x: columns>
        <m x: DataGridColumn headerText="Name" dataField="name" editable="false"/>
        <m x: DataGridColumn headerText="Author" dataField="author" width="115"
           editable="false"/>
        <m x: DataGridColumn headerText="Rating" dataField="rating" width="50"
           editable="true" />
      </m x: columns>
    </m x: DataGrid>
    <m x: Button x="10" y="259" label="UpdateDatabase" id="butUpdate"
       click="{sendPHPData()}"/>
    <m x: Label x="140" y="261" id="lblStatus"/>
  </m x: Panel>
</m x: Application>

So this should give you a pretty good handle on getting data from MySQL to Flex and back again. If you have any questions or concerns please leave a comment and we will try and reply as quickly as possible.

A few quick notes: Data concurrency is not taken into account in this tutorial, this could be handled on the php side when we try to update, though. Also we send all the data back to php when we update, we could have done this a couple different ways. We could have sent individual data pieces back everytime something is updated, we could just send back what was updated when the button was pressed by keeping track of what was changed since the last update. Both of those (and other possible methods) require more code and our more complex, but can be implemented with the same flexjsonphp techniques used here.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s