How to implement server-side processing with jQuery DataTables plugin?

Question

I am using the jQuery DataTables plugin to display a set of results combined from multiple tables. I am trying to implement server-side processing through the plugin to improve page load times. I can't quite figure out how to extrapolate the example DataTables gives to my scenario. Any ideas/suggestions?

Javascript:

$('#results').dataTable({
        "sAjaxSource": "../server_processing.php",  
        "bProcessing": true,
        "bServerSide": true,
        "bDeferRender": false, 
})

Original PHP Function:

function build_data_list(){

    global $org_id;
    global $dbh;
    global $req_ids;
    global $user_list;

    $sth = $dbh->query ("SELECT 
                        l4.name as L4name, 
                        l3.name as L3name, 
                        l2.name as L2name, 
                        l1.name as L1name,
                        u.id,
                        u.last_name,
                        u.first_name,
                        FROM user_grp_indx ugi, groups l4, groups l3, groups l2, groups l1, users p
                        WHERE
                        ugi.user_id = u.id
                        AND l4.id =  ugi.grp_id
                        AND l4.parent = l3.id
                        AND l3.parent = l2.id
                        AND l2.parent = l1.id
                        ORDER BY u.id", PDO::FETCH_ASSOC);

    $row = $sth->fetch();

    $item['user_id'] =  $row['id'];

    $item['user_info'] = '<a href="../users/index.php?pq=';
    $item['user_info'] .= $row['id'].'">';
    $item['user_info'] .= $row['last_name']. ", " . $row['first_name'] . "</a>";    

    $item['l1_name'] = $row['L1name'];
    $item['l2_name'] = $row['L2name'];
    $item['l3_name'] = $row['L3name'];
    $item['l4_name'] = $row['L4name'];

    for ($i=0; $i < sizeof($req_ids) ; $i++ ){
            $item['req'.$i] = (chk_req_status($item['user_id'],$req_ids[$i]) ? "<span title=\"Yes\"></span><img src=\"../../media/icons/tick.png\" alt=\"Yes\" />" :
                                                    "<span title=\"No\"></span><img src=\"../../media/icons/cross.png\" alt=\"No\" />");
    }

    $old_L1id = $row['L1id'];
    $old_user_id = $row['id'];

    while ($row = $sth->fetch()){

        $L1id =  $row['L1id'];
        $user_id =  $row['id'];

        if ($L1id == $old_L1id && $user_id == $old_user_id ){

            $item['l2_name'] .= "<br/>" . $row['L2name'];
            $item['l3_name'] .= "<br/>" . $row['L3name'];
            $item['l4_name'] .= "<br/>" . $row['L4name'];

            continue;

        }   

        $user_list[] = $item;     
        $old_L1id = $L1id; 
        $old_user_id = $user_id;

        $item['user_id'] =  $row['id'];

        $item['user_info'] = '<a href="../users/index.php?pq=';
        $item['user_info'] .= $row['id'].'">';
        $item['user_info'] .= $row['last_name']. ", " . $row['first_name'] . "</a>";  

        //add inital level stuff to the new record.
        $item['l1_name'] = $row['L1name'];
        $item['l2_name'] = $row['L2name'];
        $item['l3_name'] = $row['L3name'];
        $item['l4_name'] = $row['L4name'];  

        for ($i=0; $i < sizeof($req_ids) ; $i++ ){
            $item['req'.$i] = (chk_req_status($item['user_id'],$req_ids[$i]) ? "<span title=\"Yes\"></span><img src=\"../../media/icons/tick.png\" alt=\"Yes\" />" :
                                                    "<span title=\"No\"></span><img src=\"../../media/icons/cross.png\" alt=\"No\" />");
        }

    }

}

Original HTML/PHP:

<tbody>
    <?php foreach($user_list as $item){

        print "<tr>";
        print "<td class=\"hidden\">{$item['user_id']}</td>";
        print "<td>{$item['user_info']}</td>";
        print "<td>{$item['l1_name']}</td>";
        print "<td>{$item['l2_name']}</td>";
        print "<td>{$item['l3_name']}</td>";
        print "<td>{$item['l4_name']}</td>";

        for ($i=0; $i < sizeof($req_ids) ; $i++ ){
            print '<td>'.(  chk_req_status($item['user_id'],$req_ids[$i]) ? "<span title=\"Yes\"></span><img src=\"../../media/icons/tick.png\" alt=\"Yes\" />":
                "<span title=\"No\"></span><img src=\"../../media/icons/cross.png\" alt=\"No\" />").'</td>';
        }

        print "</tr>";  

    } ?>
</tbody>

Is your PHP emitting HTML or JSON? Oughtn't it to be JSON emitted after the initial HTML, since DataTables is going to be asking for JSON when the user changes the table's displayed page? — Allan

John Green John Green · Accepted Answer · 2013-11-28T15:46:32

Can this be of any help?

Server-side processing | PHP with MySQL

Server-side processing | PHP with MySQL This script serves as the basis for the testing and development that happens with DataTables, so it is always right up to date, and will always implement all of the features that are supported in DataTables with server-side processing (with the exception of regex filtering - for database access speed reasons).

To use the code on your own server, simply change the $aColumns array to list the columns you wish to include from your database, set $sIndexColumn to a column which is indexed (for speed), $sTable to the table name, and finally fill in your database connection parameters to $gaSql. Please note that this script uses json_encode which requires PHP 5.2 or newer. For a version of this script which is compatible with older versions please see the PHP 4 compatible version.

How to implement server-side processing with jQuery DataTables plugin?

1 Answers