server_processing.php for DataTables

The server_processing.php script provided by Allan at http://www.datatables.net/release-datatables/examples/data_sources/server_side.html (and in the download zip file, in the examples directory) is 174 lines of utility that covers ALMOST everything you would need in a server-side script for DataTables.

This blog entry will walk through each line of the script (just the MySQL/PHP version) and provide details, explanations, and even pointers for making changes.

Summary

Before I begin, I'll point out a few things to look for as we proceed. You should glance at http://www.datatables.net/usage/server-side to see which params are passed into this script and which values it is expected to return.

Important parameters and variables include:
$sSearch - the global search value sent by the client script
$sSearch_{$i} - column-specific search values (i.e. $sSearch_3) sent by the client script
$bSearchable_{$i} - column specific options. also $bSortable_{$i}, $bRegex_{$i} sent by the client script

$sColumns - not documented, but useful param or db fields for query sent to server if you specify sName on columns (if aoColumns are unspecified, default seems to be to use mDataProp for columns, and $sColumns is empty)
$aColumns - array of db fields to query

$sQuery - constructed SQL query
$sTable - table name to use with $sQuery
$sWhere - WHERE clause to use with $sQuery
$sOrder - ORDER BY clause to use with $sQuery
$sLimit - LIMIT clause to use with $sQuery

Always "sanitize" input values by running them through mysql_real_escape_string() or a similar escape function to avoid injection attacks or invalid characters in your queries.

The code

<?php

Your code of course starts and ends with PHP tags to enclose code.

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );

The $aColumns variable is hardcoded into the script - you will need to edit this to reflect your column names (or add the tweak below). This is the list of field names that will be used in the SELECT query against the database. Here is an opportunity for improvement: if $sColumns is passed in, we can convert that to $aColumns and override the hard-coded values:

// use columns passed in as $sColumns, if any. over-ride the $aColumns array.
if (isset($_GET['sColumns']) && $_GET['sColumns']) {
$sColumns = $_GET['sColumns'];
$aColumns = explode(',', $sColumns);
}

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";

/* DB table to use */
$sTable = "ajax";

$sIndexColumn is your primary key or other indexed column which will speed up queries regarding result set size. Make sure you have an index, preferably a primary key index, on this column.

$sTable is your table name, so edit this value as well.

/* Database connection information */
$gaSql['user'] = "";
$gaSql['password'] = "";
$gaSql['db'] = "";
$gaSql['server'] = "localhost";

/* REMOVE THIS LINE (it just includes my SQL connection user/pass) */
include( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" );

Edit these four values to reflect your database connection settings. The include line is only used if you declare those connection values in a separate file. Usually this is done in order to put password info in a separate directory with extra protections like .htaccess or permission restrictions.

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

True.. true - you don't need to edit anything below. But there are a few opportunities for change in certain scenarios where you need a little more than the default script provides.

/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );

This obviously makes the MySQL connection. If any errors are encountered, the script will die() with error messages. For more details, you can get more info such as the MySQL error number and error description with:

$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server\n'.mysql_errno() . ': '.mysql_error() );

mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] . '\n'.mysql_errno() . ': '.mysql_error() );

/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}

If you do any debugging and you have a large data set, I really recommend setting a default $sLimit, so that your result set will be smaller unless $iDisplayStart and $iDisplayLength is sent from the client.

$sLimit = "LIMIT 50";

/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
{
if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
}
}

$sOrder = substr_replace( $sOrder, "", -2 );
if ( $sOrder == "ORDER BY" )
{
$sOrder = "";
}
}

The relevant parameters sent for sorting are (note: neither $i nor $j are variable names used in the script. I just use them for descriptive purposes here)

• $iSortingColumns - number of sortable columns, you see this used in the for loop
• $iSortCol_{$i} - integer representing one of the $aColumns (note: $i is not the column number, $iSortCol_{$i} is
• $bSortable_{$j} - boolean if column $j is sortable (I used $j rather than $i because $j is $iSortCol_{$i}, not $i)
• $sSortDir_{$i} - string that describes the direction of this sort ('asc' or 'desc')

This portion of code assembles the ORDER BY clause for $sQuery by checking all $iSortCol columns specified by the client script, verify that they are sortable, and writing them in order of sort preference. Sort order matters, and $j (aka $iSortCol_{$i}) is not necessarily in numerical order. For instance if you wanted to first sort by column 7, then by column 3, $iSortCol_0 might be column 7 and $iSortCol_1 might be column 3.

/*
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}

There are 2 filtering methods to consider for the WHERE clause: global and column-specific filtering. This is the global filter. If $sSearch is sent from the client, search all columns for that value.

A small oversight here is that the script does not check if the column is marked bSearchable. Here is a fix:

{
if (isset($_GET['sSortable_'.$i]) && $_GET['sSortable_'.$i] == "true")
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}

Another change you might want to make, since MySQL supports the REGEXP operator (aka RLIKE), is to detect $bRegex_{$i} and use REGEXP instead of LIKE:

{
if (isset($_GET['sSortable_'.$i]) && $_GET['sSortable_'.$i] == "true") {
if(isset($_GET['bRegex_'.$i]) && $_GET['bRegex_'.$i] == "true")
$sWhere .= $aColumns[$i]." REGEXP '".mysql_real_escape_string( $_GET['sSearch'] )."' OR ";
else
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
}
}

/* Individual column filtering */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
}
}

This is the column-specific filter. It is very similar, but only adds a single column's field name to the WHERE clause.

/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

We finally put it all together into a single $sQuery variable. The field names from $aColumns are glued together as a string with implode() and run against our $sTable using our $sWhere, $sOrder, and $sLimit variables.

SQL_CALC_FOUND_ROWS is an optimization directive that tells MySQL to remember the number of rows found so we can query it (see next query below).

If you are having issues with your query or results, this would be a good time to keep a copy of the $sQuery so that you can send it back with the JSON to your client. This allows you to read over your query to spot errors.

$sSaveQuery = $sQuery; // we will use this value later when we create our return object's JSON

/* Data set length after filtering */
$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

We clobber the previous $sQuery (which is why I created $sSaveQuery to cache that value) in order to retrieve the number of rows that meet our filter criteria. This is important for the pagination controls on the client side, so we store in $iFilteredTotal and will return this with the JSON response.

/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];

We also need to get the total number of rows in the database. The client side will present messages such as "filtered from X,XXX rows", so we need to return that total as well with the JSON. By making sure that the $sIndexColumn is an indexed column, and preferably a primary key, this query is very quick - MySQL just has to check the "cardinality" already stored in the index.

/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);

We are now creating the object that will be returned as JSON. Though we are using a PHP array, json_encode() will map associative (string-based) arrays to Javascript Objects.

If we saved the $sSaveQuery, this would be the place to add it to the return object. You can return any data you want with this object. The user doesn't see this data unless you use it in your app, but you can access it in your code or just view it in the debugger.

$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array(),
"sSaveQuery" => $sSaveQuery
);

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}

This loop just goes through the result set from our original query and adds it to the aaData array. If you want to use the object form of aaData and mDataProp, use string keys instead of numeric keys to your arrays. [I may revise this section to shown an example of the object form of aaData.]

echo json_encode( $output );
?>

Finally, the script encodes the output object to JSON and echoes the value.

As mentioned in another post, if you don't have PHP 5.2+, you might not have json_encode(); you can download a substitute at http://www.boutell.com/scripts/jsonwrapper.html.

Conclusion

That's about it. It's not a terribly complicated piece of code, but it's great that it's already provided for you and the default script performs most functions that you would want without requiring any edit from you other than database connection values.

Enjoy.