Datatables and Wordpress Integration problem solved.

Note: Download a working demo plugin from this project's github repository.

Like other wordpress developers, I had also faced the problem of integrating datatable(server side processing) with wordpress. I tried every tips, tricks and multiple ways to solve the problem, but couldn't do it. My ajaxified functions of the wordpress simple dint work. I din't have much time and had to finish a small project on time. So, I decided to skip it and go through the developer's way : the plugin developer had suggested and implemented.

I dropped the required php file with some related modifications and called the url of the php file in the datatable options in my project. It worked after few small hiccups. I wasn't satisfied though, as i wanted the wordpress centric solution, instead of using some drop-in file to complement the functionality. I had vowed to look after this problem whenever I could get some time.




(You may visit the url http://www.bearinghouse.net/order-now/ to see it working. It is working with the drop-in php file with some modifications). Correction: It is working with the Ajaxified Wordpress Solutions discussed below.


Actually, to tell you the truth, I am lazy. I want to solve the problem in such a way that I do not have to spend too much time on that. Due to this laziness of mine, I am always on the lookout for sneaky ways to get the job done quickly. Though, I never sacrifice quality, and usefulness because of my laziness.

So, like I said, I wanted to revisit the problem I had faced with datatable and wordpress earlier.

As mentioned earlier, I was not satisfied with the drop-in php file and wanted to fully integrate server side processing feature of datatable with wordpress. So, I started researching again and read the following articles to get inspired.

http://datatables.net/forums/discussion/6759/wordpress-integration/p1
http://datatables.net/release-datatables/examples/server_side/post.html
http://datatables.net/release-datatables/examples/server_side/server_side.html
http://wp.smashingmagazine.com/2011/10/18/how-to-use-ajax-in-wordpress/
http://codex.wordpress.org/AJAX_in_Plugins
http://www.garyc40.com/2010/03/5-tips-for-using-ajax-in-wordpress/

and suddenly it clicked that I had been looking at the problem with a generalized view. So much for my lateral thinking training. Lets get back to our code editors to see how I solved the problem. I am using the same datatable used in all examples here.

My js code for datatable is:

oTable = jQuery('#example').dataTable({

   "bProcessing": true,

   "bServerSide": true,

   "sAjaxSource": ajaxurl+'?action=fn_my_ajaxified_dataloader_ajax',

   "bDeferRender": true,

   "fnServerData": fnDataTablesPipeline,

"bScrollInfinite": true,

        "bScrollCollapse": true,

        "sScrollY": "200px"

});

My Wordpress PHP Code:

Define Ajax Actions

add_action('wp_ajax_fn_my_ajaxified_dataloader_ajax', 'fn_my_ajaxified_dataloader_ajax');
add_action('wp_ajax_nopriv_fn_my_ajaxified_dataloader_ajax', 'fn_my_ajaxified_dataloader_ajax');

Now the wordpress Function

function fn_my_ajaxified_dataloader_ajax()
{
global $wpdb;

$aColumns = array( 'engine', 'browser', 'platform', 'version', 'grade' );
$sIndexColumn = "id";
$sTable = "ajax";

$sLimit = "";
if ( isset( $_REQUEST['iDisplayStart'] ) && $_REQUEST['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_REQUEST['iDisplayStart'] ).", ".
intval( $_REQUEST['iDisplayLength'] );
}

$sOrder = "";
if ( isset( $_REQUEST['iSortCol_0'] ) )
{
$sOrder = "ORDER BY  ";
for ( $i=0 ; $i<intval( $_REQUEST['iSortingCols'] ) ; $i++ )
{
if ( $_REQUEST[ 'bSortable_'.intval($_REQUEST['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= "`".$aColumns[ intval( $_REQUEST['iSortCol_'.$i] ) ]."` ".
($_REQUEST['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
}
}

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

$sWhere = "";
if ( isset($_REQUEST['sSearch']) && $_REQUEST['sSearch'] != "" )
{
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
$sWhere .= "`".$aColumns[$i]."` LIKE '%".esc_sql( $_REQUEST['sSearch'] )."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}

for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( isset($_REQUEST['bSearchable_'.$i]) && $_REQUEST['bSearchable_'.$i] == "true" && $_REQUEST['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
$sWhere .= "`".$aColumns[$i]."` LIKE '%".esc_sql($_REQUEST['sSearch_'.$i])."%' ";
}
}

$sQuery = "
SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
FROM   $sTable
$sWhere
$sOrder
$sLimit
";
$rResult = $wpdb->get_results($sQuery, ARRAY_A);

$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = $wpdb->get_results($sQuery, ARRAY_N);
$iFilteredTotal = $rResultFilterTotal [0];

$sQuery = "
SELECT COUNT(`".$sIndexColumn."`)
FROM   $sTable
";
$rResultTotal = $wpdb->get_results($sQuery, ARRAY_N);
$iTotal = $rResultTotal [0];

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

foreach($rResult as $aRow)
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "version" )
{
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}

echo json_encode( $output );
die();
}

This conversion may not be perfect, but atleast it is letting me do my job. Yes, I am using $_REQUEST. It worked the first time, so I dint try $_POST. Why don't you try that and let me know so that I can mention here that $_POST works.

Hope you can get it to work. If not let me know and I'll fix your problem or guide you in the right direction.

Niraj Kumar

*Update : It seems that I should also have used ARRAY_N instead of ARRAY_A. Well, the above code is fixed now. No problems anymore.
*Update: I missed the bugs. Thanks Naab for pointing out the problems.
* Update: Yes Post works too.