Data Grid Part III

In the previous article, I covered the javascript file line-by line. Now we'll go through the back-end php script to see how to process the AJAX request and return the data so ext can load it into the grid.

$link = mysql_pconnect("server", "username", "password") or die("Could not connect");
mysql_select_db("database") or die("Could not select database");

$sql_count = "SELECT * FROM tb_termbase WHERE tb_user = '".$_POST['client']."'";

if(isset($_REQUEST['filter']) && $_REQUEST['filter'] != '') {
    $sql_count .= "AND (english LIKE '%".$_REQUEST['filter']."%' OR german LIKE '%".$_REQUEST['filter']."%' OR french LIKE '%".$_REQUEST['filter']."%' OR italian LIKE '%".$_REQUEST['filter']."%')";
}

$sql = $sql_count . " ORDER BY ".$_POST['sort']." ".$_POST['dir']." LIMIT ".(integer)$_POST['start'].", ".(integer)$_POST['limit'];

$rs_count = mysql_query($sql_count);

$rows = mysql_num_rows($rs_count);

$rs = mysql_query($sql);

while($obj = mysql_fetch_object($rs))
{
	$arr[] = $obj;
}

echo $_REQUEST['callback'].'({"total":"'.$rows.'","results":'.json_encode($arr).'})';

Connecting to the Database

Since this is an independent script running outside of MODx, it doesn't have access to the MODx API. So we need to connect to the database ourselves.

 

$link = mysql_pconnect("server", "username", "password") or die("Could not connect");
mysql_select_db("database") or die("Could not select database");

A really nice feature of extjs is that it will generate an error log in the same folder as the script, so even though these messages will never show on the screen, if your grid isn't working you can check the error log where they will be recorded.

Building the Queries

Next is the usual tedious setup of the query. We need to check the POST values that were sent by the AJAX request and incorporate them into the query to filter it and sort it.

$client = mysql_real_escape_string($_POST['client']);
$sql_count = "SELECT * FROM table WHERE client = '".$client."'";

if(isset($_POST['filter']) && $_POST['filter'] != '') {
    $filter = mysql_real_escape_string($_POST['filter']);
    $sql_count .= "AND (english LIKE '%".$filter."%' OR german LIKE '%".$filter."%' OR french LIKE '%".$filter."%' OR italian LIKE '%".$filter."%')";
}

$sort = mysql_real_escape_string($_POST['sort']);
$dir = mysql_real_escape_string($_POST['dir']);
$start = is_numeric($_POST['start']) ? (integer)$_POST['start'] : 0;
$limit = is_numeric($_POST['limit']) ? (integer)$_POST['limit'] : 25;
$sql = $sql_count . " ORDER BY ".$sort." ".$dir." LIMIT ".$start.", ".$limit;

GOTCHA Notice that I typecast the start and limit values to integer. POST values are sent as strings, and the database needs an integer for these values.

All of the values are sanitized or validated to minimize the chances of evil-intended people trying to break the site with SQL injection. You should never, ever trust any data coming in from any outside source, including your own javascript.

Determining when ext sends by GET or by POST took a while. If you send extra parameters, then it uses POST. But if it's just a straight default request, it uses GET.

Another issue that took a while to discover was exactly what parameters does the paging and auto-sort functions send? I finally found the sort parameters in the API documentation under the Ext -> data -> store item, defined in the Remote Sort config option description.

If remote sorting is specified, then clicking on a column header causes the current page to be requested from the server with the addition of the following two parameters:

    * sort : String
      The name (as specified in the Record's Field definition) of the field to sort on.
    * dir : String
      The direction of the sort, "ASC" or "DESC" (case-sensitive).

I never did find the documentation for the paging parameters, but the demo I built this from had "start" and "limit", so it's fairly obvious those are what the php file needs to look for.

Running the Queries

First, we just run the basic SELECT query, in my case filtered by the username, and if it's a search request filtered by the search criteria, to get the count of the total number of records.

$rs_count = mysql_query($sql_count);
$rows = mysql_num_rows($rs_count);

Then we run the full combined query, with paging details and all.

$rs = mysql_query($sql);

The data returned by the second query is extracted and loaded into an array.

while($obj = mysql_fetch_object($rs))
{
    $arr[] = $obj;
}

And finally, the results are packaged up into a json-encoded string, and the AJAX callback function is invoked with the parameters for the total number of rows and the json string of results.

echo $_REQUEST['callback'].'({"total":"'.$rows.'","results":'.json_encode($arr).'})';

If you remember our reader object, it was defined with these parameters, so it can unpack and provide the data to the grid for display.

        reader: new Ext.data.JsonReader({
            root: 'results',
            totalProperty: 'total',