2. Basic Usage v1.2.0

This example shows how you would use CakePHP-Datatables to display data for a single table. This tutorial includes three parts.

• Basic Usage
• Modifying data within datatables
• Searching and sorting data

Let's assume we have a users table that looks like this contains:

• id
• username
• email
• created
• active
• password

Example 1 - Displaying a basic table

In the example below we want to display the username, email, and created timestamp.

class UsersController extends AppController {
    
    public $components = array('DataTable');

    function index(){
        $this->paginate = array(
            'fields' => array('User.username','User.email', 'User.created'),
        );
/**
Note: The mDataProp attribute is new in version 1.2. This returns JSON data in CakePHP format. 
Prior to 1.2 data was returned in a key-value pair and was cumbersome to work with. It is recommended 
you use the mDataProp. It's disabled by default to maintain backwards compatibility but will be made 
default in the future.
**/
        $this->DataTable->mDataProp = true;
        $this->set('response', $this->DataTable->getResponse());
        $this->set('_serialize','response');
    }

In our HTML we have a table that looks like this:

<table id="users-table">
    <thead>
        <th>Username</th>
        <th>Email</th>
        <th>Created</th>
    </thead>
    <tbody>
    </tbody>
</table>

So our JavaScript will look like this:

$('#users-table').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "/users/index.json",
    "aoColumns": [
        {mData:"User.username"},
        {mData:"User.email"},
        {mData:"User.created"}
    ],
});

Example 2 - Modifying data within the table to display a hyperlink to a user record

Expanding from our example above what if we wanted to create a hyperlink to the users record within our table and only show active users? This example will show you how to do just that:

class UsersController extends AppController {
    
    public $components = array('DataTable');

    function index(){
        $this->paginate = array(
            // in this example we've removed the fields element to show you its not required
            'conditions' => array(
                'active'=>1
            )
        );
        $this->DataTable->mDataProp = true;
        $this->set('response', $this->DataTable->getResponse());
        $this->set('_serialize','response');
    }

Our HTML markup remains the same so lets move into the JavaScript:

$('#users-table').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "/users/index.json",
    "aoColumns": [
        {mData:"User.username"},
        {mData:"User.email"},
        {mData:"User.created"}
    ],
    "fnCreatedRow": function(nRow, aData, iDataIndex){
        $('td:eq(0)', nRow).html('<a href="/users/view/'+aData.User.id+'">'+aData.User.username+'</a>');
    }
});

We tell aoColumns to only display username, email, and created, but we can still access everything the server returned within the fnCreatedRow callback.

Example 3 - Basic sorting and searching

We'll continue on from Example 2 to show how easy it is to provide your users with basic sorting and searching functionality. Our PHP code from Example 2 a long with the HTML markup from Example 1 will remain the same. In fact because CakePHP-Datatables integrates with DataTables so well you only have to make a minor change to your JavaScript:

$('#users-table').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "/users/index.json",
    "sDom": "fr",
    "aoColumns": [
        {mData:"User.username"},
        {mData:"User.email"},
        {mData:"User.created"}
    ],
    "fnCreatedRow": function(nRow, aData, iDataIndex){
        $('td:eq(0)', nRow).html('<a href="/users/view/'+aData.User.id+'">'+aData.User.username+'</a>');
    }
});

By adding the "r" sDom attribute we've added a search box to datatables. Typing into this search box will automatically call /users/index.json and merge conditions into your default conditions supplied to the CakePHP paginator and return the results back to dataTables. You can also tell it what you don't want to search on and what you don't want to sort on: Let's also tell it we only want to display 20 records per page.

$('#users-table').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "/users/index.json",
    "sDom": "fr",
    "iDisplayLength": 20,
    "aoColumns": [
        {mData:"User.username"},
        {mData:"User.email", bSortable: false},
        {mData:"User.created", bSearchable: false}
    ],
    "fnCreatedRow": function(nRow, aData, iDataIndex){
        $('td:eq(0)', nRow).html('<a href="/users/view/'+aData.User.id+'">'+aData.User.username+'</a>');
    }
});

In the above code we've told our table to not sort on the email column and to not search on the created column. These configurations can be handy in optimizing lookups and avoiding sorts on unnecessary data.