Data Paging with Flex and PHP in Flash Builder

Flash Builder 4 can really speed up the development of data-driven applications. There are new wizards that can help you create the data service and the data model on the client side, and map client side operations to server side operations (CRUD applications). This post is an introduction to the new Data/Services view from Flash Builder 4 (I am using a PHP server and a PHP class).

In this article I will focus on how to use the available wizards to do data paging. While this new feature can handle ColdFusion, Java, REST style services, and SOAP services, in this post I will use PHP. Data paging is very useful when you have large sets of data. Basically, using this feature, you can bind a data grid to a server operation that manages a large table, and only 20 records will be sent to the client at one time. As you scroll through the data grid and reach areas where you don’t have data yet, a new request is made for that region and another page of records is brought into the client.

PHP code

On the server side I have two classes. One is the data model for my data, VOEmployee:

   1: <?php
   2: class VOEmployee {
   3:     
   4:     public $emp_no;
   5:     public $birth_date;
   6:     public $first_name;
   7:     public $last_name;
   8:     public $gender;
   9:     public $hire_date;
  10:     public $phone_no;
  11:     public $email_address;
  12:     public $job_title;
  13: }
  14: ?>

This class wraps a record from the MySQL table. The second class is the PHP service itself, Employees.php.

   1: <?php 
   2: require_once 'VOEmployee.php';
   3:  
   4: //conection info
   5: define("DATABASE_SERVER", "localhost");
   6: define("DATABASE_USERNAME", "mihai");
   7: define("DATABASE_PASSWORD", "mihai");
   8: define("DATABASE_NAME", "employees");
   9:  
  10: /**
  11: $o = new Employees();
  12: $o->count();
  13: */
  14:  
  15: class Employees {
  16:     
  17:     private $_con;
  18:     
  19:     public function Employees() {
  20:         $this->_con = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD);
  21:         if (!$this->_con) 
  22:             $this->throwError();
  23:         mysql_select_db(DATABASE_NAME);
  24:     }
  25:     
  26:     public function getAllItems($lastName) {
  27:         if ($lastName==NULL) {
  28:             $sql = 'SELECT * FROM employees';
  29:         } else {
  30:             $sql = 'SELECT * FROM employees WHERE last_name LIKE \'%'.mysql_escape_string($lastName).'%\'';
  31:         }
  32:         $result = mysql_query($sql, $this->_con);
  33:         if (!$result) 
  34:             $this->throwError();
  35:         $ret = array();
  36:         while ($row = mysql_fetch_object($result, "VOEmployee")) {
  37:             $ret[] = $row;
  38:         }
  39:         mysql_free_result($result); 
  40:         return $ret;
  41:     }
  42:     
  43:     public function getItem($itemID) {
  44:         $sql = 'SELECT * FROM employees WHERE emp_no = '.mysql_escape_string($itemID);
  45:         $result = mysql_query($sql, $this->_con);
  46:         if (!$result) 
  47:             $this->throwError();
  48:         $ret = mysql_fetch_object($result, "VOEmployee");
  49:         mysql_free_result($result);
  50:         
  51:         return $ret;
  52:     }
  53:  
  54:     public function createItem($item) {
  55:         $sql = 'INSERT INTO employees (first_name, last_name, gender, email_address, hire_date, birth_date, job_title) VALUES (';
  56:         $sql .= '\'' . mysql_escape_string($item->first_name) . '\', ';
  57:         $sql .= '\'' . mysql_escape_string($item->last_name) . '\', ';
  58:         $sql .= '\'' . mysql_escape_string($item->gender) . '\', ';
  59:         $sql .= '\'' . mysql_escape_string($item->email_address) . '\', ';
  60:         $sql .= '\'' . mysql_escape_string($item->hire_date) . '\', ';
  61:         $sql .= '\'' . mysql_escape_string($item->birth_date) . '\'';
  62:         $sql .= ')';
  63:         $result = mysql_query($sql, $this->_con);
  64:         if (!$result) 
  65:             $this->throwError();
  66:         return NULL;
  67:     }
  68:     
  69:     public function updateItem($item) {
  70:         $sql = 'UPDATE employees SET ';
  71:         $sql .= 'first_name = \'' . mysql_escape_string($item->first_name) . '\', ';
  72:         $sql .= 'last_name = \'' . mysql_escape_string($item->last_name) . '\', ';
  73:         $sql .= 'gender = \'' . mysql_escape_string($item->gender) . '\', ';
  74:         $sql .= 'email_address = \'' . mysql_escape_string($item->email_address) . '\', ';
  75:         $sql .= 'hire_date = \'' . mysql_escape_string($item->hire_date) . '\', ';
  76:         $sql .= 'birth_date = \'' . mysql_escape_string($item->birth_date) . '\', ';
  77:         $sql .= 'job_title = \'' . mysql_escape_string($item->job_title) . '\'';
  78:         $sql .= ' WHERE emp_no = ' . mysql_escape_string($item->emp_no);
  79:         
  80:         $result = mysql_query($sql, $this->_con);
  81:         if (!$result) {
  82:             $this->throwError();
  83:         }    
  84:         return NULL;
  85:     }
  86:     
  87:     public function deleteItem($itemID) {
  88:         $sql = 'DELETE FROM employees WHERE emp_no = ' . mysql_escape_string($itemID);
  89:         $result = mysql_query($sql, $this->_con);
  90:         if (!$result) 
  91:             $this->throwError();
  92:         return NULL;
  93:     }
  94:     
  95:     public function count() {
  96:         $sql = 'SELECT COUNT(emp_no) AS empCount FROM employees';
  97:         $result = mysql_query($sql, $this->_con);
  98:         if (!$result) 
  99:             $this->throwError();
 100:         $row = mysql_fetch_object($result);
 101:         $ret = $row->empCount + 0;
 102:         mysql_free_result($result);
 103:         return $ret;
 104:     }
 105:     
 106:     public function getItems_paged($startIndex, $numItems) {
 107:         $sql = 'SELECT * FROM employees LIMIT ' . mysql_escape_string($startIndex) . ', ' . mysql_escape_string($numItems);
 108:         $result = mysql_query($sql, $this->_con);
 109:         if (!$result) 
 110:             $this->throwError();
 111:             
 112:         $ret = array();
 113:         while ($row = mysql_fetch_object($result, "VOEmployee")) {
 114:             $ret[] = $row;
 115:         }
 116:         mysql_free_result($result); 
 117:         return $ret;
 118:     }
 119:  
 120:     private function throwError($message="") {
 121:         if ($this->_con)
 122:             $message .= 'Error no: ' . mysql_errno($this->_con) . '. Message: ' . mysql_error($this->_con);
 123:         throw new Exception($message, 1);
 124:     }
 125: }
 126:  
 127:     
 128: ?>

While this service implements all the CRUD operations, actually I’ll use only two methods for my example: getItems_paged() and count().

Flex code

On the client, I created a Flex project that uses PHP as the server side technology, and then I added a data grid.

   1: <?xml version="1.0" encoding="utf-8"?>
   2: <s:Application xmlns:fx="http://ns.adobe.com/mxml/2009" xmlns:s="library://ns.adobe.com/flex/spark" xmlns:mx="library://ns.adobe.com/flex/halo" minWidth="1024" minHeight="768">
   3:     <mx:DataGrid x="42" y="40">
   4:         <mx:columns>
   5:             <mx:DataGridColumn headerText="Column 1" dataField="col1"/>
   6:             <mx:DataGridColumn headerText="Column 2" dataField="col2"/>
   7:             <mx:DataGridColumn headerText="Column 3" dataField="col3"/>
   8:         </mx:columns>
   9:     </mx:DataGrid>
  10:     <s:layout>
  11:         <s:BasicLayout/>
  12:     </s:layout>
  13: </s:Application>

Next, using the Data/Services panel you can add a new data service (choose PHP from the wizard’s first page), and point the wizard to the PHP class Employees.php. After completing this step, you need to define the Return Type for the getItems_paged() method. Right-click on the method and choose Return type. Next add a name (Employee) and make sure you select the int type for the two arguments. Finally set some values for these two arguments 1 and 20, for example.

All these steps I’ve already explained in detail in my previous post. Now let’s add the paging capabilities. Right-click on the getItems_paged() method from the Data/Services view, and choose from the menu Enable Paging…. In the first page of the wizard select the primary key property of the Employee data model:

paging1

And, in the second page select the count method from the dropdown (this step is optional; if you don’t specify a count() method, then when the data grid is rendered the height of the scrollbar’s thumb is not correlated with the number of the records. Usually the more data you have, the thinner the thumb will be):

paging2

Click Finish, and then bind the getItems_paged() operation to the data grid (Go to Design mode, select the operation from the Data/Services view and drag-and-drop over the data grid). And actually this is all you have to do. This is how the Employees data services looks:

paging3

When you run the application, it should look like this:

paging4

Changing the Page size

You’ve probably noticed that the wizard doesn’t provide any means to control the size of the page. And if you take a look at the Network Monitor and you inspect the result, you’ll see that the page size is set to 20 records.

If you want to change the size, you can do it programmatically using the property pageSize of the DataManager class (you can see the doc for this class here). And you can obtain the DataManager instance for your service using the getDataManager() method on the service. The code looks like this:

   1: var d:DataManager = employees.getDataManager(employees.DATA_MANAGER_EMPLOYEE);
   2: d.pageSize = 10;
   3:  
   4: [...]
   5:  
   6: <employees:Employees id="employees" destination="Employees" 
   7:    endpoint="http://localhost/dataPaging-debug/gateway.php" 
   8:    fault="Alert.show(event.fault.faultString)" 
   9:    showBusyCursor="true" source="Employees"/>

What could go wrong

If you are not able to add data paging to your data service, the PHP method probably doesn’t have the correct number of arguments or the arguments don’t have the expected names (please don’t ask me why it has to be this way). Here is the error message:

paging5

Secondly, when you run your application you could see this error:

paging6

   1: ArgumentError: null
   2:     at mx.data::RPCDataServiceAdapter/countQueryResult()[C:\depot\DataServices\branches\dune_beta1\frameworks\projects\data\src\mx\data\RPCDataServiceAdapter.as:1426]
   3:     at mx.collections::ItemResponder/result()[E:\dev\beta1\frameworks\projects\framework\src\mx\collections\ItemResponder.as:129]
   4:     at mx.rpc::AsyncToken/http://www.adobe.com/2006/flex/mx/internal::applyResult()[E:\dev\beta1\frameworks\projects\rpc\src\mx\rpc\AsyncToken.as:239]
   5:     at mx.rpc.events::ResultEvent/http://www.adobe.com/2006/flex/mx/internal::callTokenResponders()[E:\dev\beta1\frameworks\projects\rpc\src\mx\rpc\events\ResultEvent.as:207]
   6:     at mx.rpc::AbstractOperation/http://www.adobe.com/2006/flex/mx/internal::dispatchRpcEvent()[E:\dev\beta1\frameworks\projects\rpc\src\mx\rpc\AbstractOperation.as:244]
   7:     at mx.rpc::AbstractInvoker/http://www.adobe.com/2006/flex/mx/internal::resultHandler()[E:\dev\beta1\frameworks\projects\rpc\src\mx\rpc\AbstractInvoker.as:318]
   8:     at mx.rpc::Responder/result()[E:\dev\beta1\frameworks\projects\rpc\src\mx\rpc\Responder.as:58]
   9:     at mx.rpc::AsyncRequest/acknowledge()[E:\dev\beta1\frameworks\projects\rpc\src\mx\rpc\AsyncRequest.as:84]
  10:     at NetConnectionMessageResponder/resultHandler()[E:\dev\beta1\frameworks\projects\rpc\src\mx\messaging\channels\NetConnectionChannel.as:538]
  11:     at mx.messaging::MessageResponder/result()[E:\dev\beta1\frameworks\projects\rpc\src\mx\messaging\MessageResponder.as:235]

Most likely, this error is due to the wrong type of the value returned by the PHP count() method. You’ll get this error if you write your code like this:

   1: $sql = 'SELECT COUNT(emp_no) AS empCount FROM employees';
   2: $result = mysql_query($sql, $this->_con);        
   3: $row = mysql_fetch_object($result);
   4: $ret = $row->empCount;
   5: mysql_free_result($result);
   6: return $ret;

In this case $ret variable will be a string, and when the values is received by the Flex code it is a int or number, and thus the error. So, just make sure you cast the $ret variable to int. You can do something like this:

   1: $ret = $row->empCount + 0;

Conclusion

You can download the project from here (in the project you’ll find in the project a folder called services; copy the two PHP files to your webserver, and use the db.sql to create the table; next make sure you set the correct credentials for database access in the Employees.php file). Finally, you might want to generate again the whole data service, in order to have the gateway.php file generated (this file is created in the Flex output folder of the project, and this folder doesn’t get exported so you won’t find it in my project).

I’m a big fan of IDE features that can save you from tedious tasks or eliminate repetitive tasks. And this feature I think saves you time. You can check for more articles on Adobe Developer Connection, or even better, download Flash Builder 4 and play with it. Be sure to let me know what you think.

6 thoughts on “Data Paging with Flex and PHP in Flash Builder

  1. Pingback: “Data paging” pomoću Flexa i PHP u novom Flash Builderu | Ivan Ilijasic.com | Web i tome slično... IvanIlijasic.com

  2. Pingback: Screencast on data paging with Flash Builder and PHP : Mihai CORLAN

  3. Pingback: Flex and PHP webinar goodies : Mihai CORLAN

  4. good tutorial, but how can I navigate through the dataprovider? I expected a prev and next buttons that play with the start index parameter…

  5. Hi, very useful tutorial.

    I’m having a weird issue… when i set my datagrid to 100% and scroll from the top to the bottom the request can be handled and the applications gets frozen!! Any ideas?

  6. Hello Mihai,

    Really great description.
    Actually I am new to Flash Builder. (Never used it)

    I wanna implement this paging in Flex Builder 3 with AdvancedDataGrid.

    any idea?

    Thank you,

    Steve

Leave a Reply

Your email address will not be published. Required fields are marked *