Skip to main content

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.
7 comments

Popular posts from this blog

Installing Oracle 9i on RHEL5. (x86)

I must warn you that this article is as it is. It worked when I was installing the combo - a long time ago. I am not sure about the links available in this article. I am not maintaining this article anymore, (other than sprucing it up a bit for better reading). I am completely out of touch with RHEL or Oracle for that matter. Still, dont lose your heart yet. I am sure, with proper searching using the big brother - Google, you will find lots of more information relating to your work to help you finish what you are doing.
Foreword
Recently I joined Sqlstar International for my Oracle DBA course. Everything was going smooth. I completed my SQL Fundamentals with Oracle installed on Windows in my pc. But when classes started for DBA Fundamentals 1 I was at loss, as in my institute oracle was installed on Redhat Linux platform. I could have done all the solution of the text on windows but I decided to configure my own Linux box and install oracle on it to simulate the class environment. So I…

Web Development Dilemma - Part 2

Recently, I got a Job to build an ERP Application (More like Accounting Package with CRM). I started planning for the Application. I had some experience with Tally.ERP 9 and knew a bit about Accounting too. Once again I chose Wordpress Platform for the application development, as I was more comfortable with it. Application was to have multiple advanced features along with some of smart automated data sensing and processing, and selective data push (Semi-Automated) to Tally for Accounting Purpose. The application was to run in a LAN Environment and was connected with the Customer's Website to retrieve Customer Order submitted on the website (Customized Shopping Cart) for further processing. 
I could develop fast in Wordpress, I was familiar with the system and environment. But due to multiple distractions, and tedious job (I hadn't anticipated lengthy coding and timing involved), the project had to be scrapped by the customer. The customer was gracious enough to forgive me, bu…