2. Adding simple Criteria, sort, and data paging with PHP
Description
This example extends sample 1 (Handling a fetch request from RestDataSource using PHP), but will have support for pagination, filtering and sorting built onto it.
(Note: although this is based on sample 1, a new solution has been built for this from the ground up, the application being built is exactly the same).
Changes to client side code
Firstly, Change the DataSource so, instead of using the URL parameters for sending data to the back-end (filter, etc), send parameters as a JSON payload to the POST method. Additionally, configure the ListGrid to let the back-end do the pagination and filtering.
For this, remove the 'dataFetchMode:"local"' attribute on the grid. This will cause the grid to send criteria and pagination information to the back-end:
isc.ListGrid.create({
"ID": "suppyItem",
"width": 700, "height": 224, "alternateRecordStyles": true,
"dataSource": suppyItem,
"showFilterEditor": true,
"autoFetchData":true,
"dataPageSize":20
});
Please note:, Grid filters have also been enabled with showFilterEditor:true. This allows for easier testing later. Also note that the dataPageSize has been set to 20, to show pagination working.
Additionally, change the DataSource definition to use POST instead of the default GET HTTP method used by normal fetch operations. This will post the JSON as a payload to the server instead of passing it the variables in a URL. To do this, add an operationBinding definition to the DataSource. The DataSource should now look like this:
isc.RestDataSource.create({
"ID": "suppyItem",
"fields":[
{"name":"itemID", "type":"sequence", "hidden":"true", "primaryKey":"true"},
{"name":"itemName", "type":"text", "title":"Item", "length":"128", "required":"true"},
{"name":"SKU", "type":"text", "title":"SKU", "length":"10", "required":"true"},
{"name":"description", "type":"text", "title":"Description", "length":"2000"},
{"name":"category", "type":"text", "title":"Category", "length":"128", "required":"true" },
{"name":"units", "type":"enum", "title":"Units", "length":"5",
"valueMap":["Roll", "Ea", "Pkt", "Set", "Tube", "Pad", "Ream", "Tin", "Bag", "Ctn", "Box"]
},
{"name":"unitCost", "type":"float", "title":"Unit Cost", "required":"true",
"validators":[
{"type":"floatRange", "min":"0", "errorMessage":"Please enter a valid (positive) cost"},
{"type":"floatPrecision", "precision":"2", "errorMessage":"The maximum allowed precision is 2"}
]
},
{"name":"inStock", "type":"boolean", "title":"In Stock"},
{"name":"nextShipment", "type":"date", "title":"Next Shipment"}
],
"dataFormat":"json",
"operationBindings": [
{ "operationType": "fetch", "dataProtocol": "postMessage", "dataURL": "fetch.php" },
]
});
As an additional change, the criteriaPolicy is now set to "dropOnChange", which will force the DataSource to send requests to the back-end each time the criteria changes, the "dropOnChange" is set in order to show the server-side search criteria functioning, because normally the grid will perform searching in the browser when all data is loaded, and this setting should be removed for production applications.
Changes on the back-end
If the sample,was run at this stage the payload sent to the server will look similar to this:
{
"dataSource":"suppyItem",
"operationType":"fetch",
"startRow":0,
"endRow":20,
"textMatchStyle":"substring",
"componentId":"suppyItem",
"data":{
"units":"Pkt",
"unitCost":"a"
...
},
"oldValues":null
}
Define an object which is created by de-serializing from JSON and which mimics the previous JSON. This should be called DSRequest:
<?php
class DSRequest
{
var $dataSource;
var $operationType;
var $startRow;
var $endRow;
var $textMatchStyle;
var $componentId;
var $data;
var $sortBy;
var $oldValues;
function __construct($params)
{
// json to array
$request = json_decode($params, TRUE);
extract($request);
$this->componentId = $componentId;
$this->dataSource = $dataSource;
$this->startRow = $startRow;
$this->sortBy = $sortBy;
$this->endRow = $endRow;
$this->oldValues = $oldValues;
$this->textMatchStyle = $textMatchStyle;
$this->data = $data;
$this->operationType = $operationType;
}
function getDataKeys()
{
$data = $this->data;
$keys = array_keys($data);
return $keys;
}
}
Also, change the controller to handle the JSON payload the front-end is sending with the POST call:
<?php
require 'DSResponse.php';
require 'rb.php';
require 'DSRequest.php';
// DSRequest
// get the JSON params
$params = $HTTP_RAW_POST_DATA;
$request = new DSRequest($params);
$query = 'Select * from supplyitem s ';
$query_count = 'select count(*) from supplyItem s';
// build up the query
if ( count($request->getDataKeys()) != 0 )
{
$query .= ' where ';
$query_count .= ' where ';
foreach($request->data as $key => $value)
{
// condition will be something like key = :key (:key being parameter placeholder)
$query .= "s." . $key . " like " . " '%" . $value . "%' and " ;
$query_count .= "s." . $key . " like " . "'%" . $value . "%' and " ;
}
// remove 'and' of the query
$query = substr($query, 0, strlen($query)- 4);
$query_count = substr($query_count, 0, strlen($query_count)- 4);
}
// sort by
if( !empty($request->sortBy) )
{
// set the orderBy
$query .= " order by ";
// we start to build a coma separated list of items. First item won't have coma
// but every possible next will do
$seperator = "";
foreach($request->sortBy as $index => $sort)
{
// if column name is with -, then ordering is descending, otherwise ascending
if ( strpos($index, '-') === 0 )
{
$query .= $seperator . $sort . " ASC";
} else
{
$query .= $seperator . substr($sort, 1) . " DESC";
}
$separator = ',';
}
}
R::setup('mysql:host=localhost;dbname=smartclient','root','apmsetup');
// get the count
$products_count = R::getAll($query_count);
$count = $products_count[0]['count(*)'];
//DSResponse
$response = new DSResponse();
$products = R::getAll($query);
R::close();
$response->setData($products);
$response->setStartRow($request->startRow);
$response->setEndRow($request->endRow);
$response->setTotalRows($count);
$response->setStatus(0);
$result['response'] = (Array)$response;
echo json_encode($result);
?>
Although the code looks a little more complicated than in the previous sample, it is actually very straight forward:
1. Create a stream to read the JSON payload as a string,The string is then used to de-serialize the DSRequest object from.
2. Using this object, create a query for getting the records, and another for getting the total count of records. Also, based on the request object, set up the sorting criteria of the records to send back.
3. Create a DSResponse object, run the queries and fill the fields of the response object and send it back to the front-end, as in the previous sample.
The complete code for this sample project can be downloaded from here.
This code is vulnerable to SQL Injection attacks, which should be fixed in a real implementation.