DataTables

• Usage
• Examples
• Styling
• API
• Development
• Editor
• Extras
• Plug-ins
• Blog
• FAQs
• Forums

• Overview
• Sorting
• Type detection
• Pagination
• API
• Filtering
• Features
• Server-side scripts
• Roadmap
• Testing

Purchase

Download

Support

Server-side processing | PHP with MongoDB

MongoDB is a popular NoSQL database solution and PHP has a native driver available to interface with MongoDB. Kari Söderholm, aka Haprog, has created this script to built a DataTables server-side processing script upon this interface, allowing DataTables to directly consume data from a MongoDB server.

Feature set

• Compatibility - DataTables 1.6+
• Pagination - yes
• Filtering - yes
• Sorting - yes
• Multi-column sorting - yes
• Individual column filter - yes
• Column filter enabled check (bSearchable_) - yes
• Column sort enabled check (bSortable_) - yes
• Regex support for filtering - yes

Code

<?php
/**
 * Script:    DataTables server-side script for PHP 5.2+ and MongoDB
 * Copyright: 2012 - Kari Söderholm, aka Haprog
 * License:   GPL v2 or BSD (3-point)
 *
 * By default Mongo documents are returned as is like they are stored in the
 * database. You can define which fields to return by overriding the empty
 * $fields array a few rows below.
 *
 * Because MongoDB documents can naturally contain nested data, this script
 * assumes (requires) that you use mDataProp in DataTables to define which
 * fields to display.
 */
mb_internal_encoding('UTF-8');

$database   = 'mydatabase';
$collection = 'mycollection';

/**
 * MongoDB connection
 */
try {
	$m = new Mongo();
} catch (MongoConnectionException $e) {
	die('Error connecting to MongoDB server');
}

$m_collection = $m->$database->$collection;

/**
 * Define the document fields to return to DataTables (as in http://us.php.net/manual/en/mongocollection.find.php).
 * If empty, the whole document will be returned.
 */
$fields = array();

// Input method (use $_GET, $_POST or $_REQUEST)
$input =& $_GET;

/**
 * Handle requested DataProps
 */

// Number of columns being displayed (useful for getting individual column search info)
$iColumns = $input['iColumns'];

// Get mDataProp values assigned for each table column
$dataProps = array();
for ($i = 0; $i < $iColumns; $i++) {
	$var = 'mDataProp_'.$i;
	if (!empty($input[$var]) && $input[$var] != 'null') {
		$dataProps[$i] = $input[$var];
	}
}

/**
 * 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 collections.
 */
$searchTermsAny = array();
$searchTermsAll = array();

if ( !empty($input['sSearch']) ) {
	$sSearch = $input['sSearch'];
	
	for ( $i=0 ; $i < $iColumns ; $i++ ) {
		if ($input['bSearchable_'.$i] == 'true') {
			if ($input['bRegex'] == 'true') {
				$sRegex = str_replace('/', '\/', $sSearch);
			} else {
				$sRegex = preg_quote($sSearch, '/');
			}
			$searchTermsAny[] = array(
				$dataProps[$i] => new MongoRegex( '/'.$sRegex.'/i' )
			);
		}
	}
}

// Individual column filtering
for ( $i=0 ; $i < $iColumns ; $i++ ) {
	if ( $input['bSearchable_'.$i] == 'true' && $input['sSearch_'.$i] != '' ) {
		if ($input['bRegex_'.$i] == 'true') {
			$sRegex = str_replace('/', '\/', $input['sSearch_'.$i]);
		} else {
			$sRegex = preg_quote($input['sSearch_'.$i], '/');
		}
		$searchTermsAll[ $dataProps[$i] ] = new MongoRegex( '/'.$sRegex.'/i' );
	}
}

$searchTerms = $searchTermsAll;
if (!empty($searchTermsAny)) {
	$searchTerms['$or'] = $searchTermsAny;
}

$cursor = $m_collection->find($searchTerms, $fields);

/**
 * Paging
 */
if ( isset( $input['iDisplayStart'] ) && $input['iDisplayLength'] != '-1' ) {
	$cursor->limit( $input['iDisplayLength'] )->skip( $input['iDisplayStart'] );
}

/**
 * Ordering
 */
if ( isset($input['iSortCol_0']) ) {
	$sort_fields = array();
	for ( $i=0 ; $i<intval( $input['iSortingCols'] ) ; $i++ ) {
		if ( $input[ 'bSortable_'.intval($input['iSortCol_'.$i]) ] == 'true' ) {
			$field = $dataProps[ intval( $input['iSortCol_'.$i] ) ];
			$order = ( $input['sSortDir_'.$i]=='desc' ? -1 : 1 );
			$sort_fields[$field] = $order;
		}
	}
	$cursor->sort($sort_fields);
}

/**
 * Output
 */
$output = array(
	"sEcho" => intval($input['sEcho']),
	"iTotalRecords" => $m_collection->count(),
	"iTotalDisplayRecords" => $cursor->count(),
	"aaData" => array(),
);

foreach ( $cursor as $doc ) {
	$output['aaData'][] = $doc;
}

echo json_encode( $output );