CRUD using jQuery and Codeigniter – VI

Learning Objectives

In this part you will learn how to perform clientside search, table sorting, limit records display at a time, and clientside pagination using jQuery.

Previous Parts

Get Source Files

Adding Table Header

Many readers were asking how to add a header in the table containing records. To add header, change the html structure in Home view.

<div id="read">
    <table id="records">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody></tbody>
    </table>
</div>

Now the table structure is changed and records fetched from the server must be appended to tbody element. So we need to change the code in all.js as shown below:

function readUsers() {
    //display ajax loader animation
    $( '#ajaxLoadAni' ).fadeIn( 'slow' );
 
    $.ajax({
        url: readUrl,
        dataType: 'json',
        success: function( response ) {
 
            for( var i in response ) {
                response[ i ].updateLink = updateUrl + '/' + response[ i ].id;
                response[ i ].deleteLink = delUrl + '/' + response[ i ].id;
            }
 
            //clear old rows (if any)
            $( '#records tbody' ).html( '' );
 
            //append new rows
            $( '#readTemplate' ).render( response ).appendTo( "#records tbody" );
 
            //hide ajax loader animation here...
            $( '#ajaxLoadAni' ).fadeOut( 'slow' );
        }
    });
} // end readUsers

On line 16 and 19, “#records” is changed to “#records tbody”. Now test your code and you will see the table header as well as shown below:

CRUD Table Header

Download DataTables

I am gonna use DataTables for Pagination, Sorting, and Search. DataTables is very powerful jQuery plugin for html tables.

Click here to download DataTables plugin. Extract the downloaded zip file and copy DataTables/media/js/jquery.dataTables.min.js to crud/js folder of crud project. For styling of DataTable, I will use jQueryUI theme. DataTable is fully compatible with jQueryUI themes (created using ThemeRoller). If you don’t want to use jQueryUI theme, then copy DataTables/media/css/demo_table.css and paste it in crud/css folder. Also copy the DataTables/media/images to crud/images folder.

Now add script file at the bottom of the view (above readTemplate) as shown below.

<script type="text/javascript" src="js/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.8.2.min.js"></script>
<script type="text/javascript" src="js/jquery-templ.js"></script>
<script type="text/javascript" src="js/jquery.validate.min.js"></script>
<script type="text/javascript" src="js/jquery.dataTables.min.js"></script>

As I mentioned before that I will use jQueryUI theme so I will not add demo_table.css in Home view.

Using DataTables

Now we are ready to use DataTables. Add dataTable variable in the end of declaration list.

var readUrl   = 'index.php/home/read',
updateUrl = 'index.php/home/update',
delUrl    = 'index.php/home/delete',
delHref,
updateHref,
updateId,
dataTable;

Then modify readUsers function to create new instance of DataTable.

//append new rows
$( '#readTemplate' ).render( response ).appendTo( "#records > tbody" );
 
//apply dataTable to #records table and save its object in dataTable variable
dataTable = $( '#records' ).dataTable({"bJQueryUI": true});

DataTable line goes right below render() in readUsers function. Then #records is selected and called dataTable() on it. This method returns DataTable object which is assigned to dataTable variable. Also note the object passed to dataTable method. It contains only one property “bJQueryUI”. This property is used to tell dataTables() to use jQueryUI theme (if it’s included in the web page). In our view, we already included smoothness jQueryUI theme. So it will automatically use that theme.

DataTable

Styling DataTable

Now the pagination, sorting, and search is fully functional. But as you can see from the above screenshot that the structure is disturbed. Also padding of top bar (Show entries and Search), header, and footer (Next/Previous buttons) is too less. Let’s correct this in styles.css.

/* DataTables specific styles */
.fg-toolbar {
    padding: 6px;
}
.dataTables_length, .dataTables_info {
    float: left;
    margin: 3px 0 0;
}
.dataTables_filter, .dataTables_paginate {
    float: right;
}
.DataTables_sort_wrapper {
    cursor: pointer;
    padding: 4px;
    position: relative;
}
.DataTables_sort_wrapper .css_right {
    position: absolute;
    right: 0;
    top: 4px;
}

I identified the classes of DataTable using Firebug and wrote CSS for it.

Fixing Row Add

When you create a new record from the Create tab, new row doesn’t appear in the table but it’s added in the database via Ajax call. We need to fix it so the record must appear in the table as well without refreshing the page. Secondly we need to prevent recreation of DataTable object in readUsers function. If you don’t prevent this, dataTable plugin gives following warning in messagebox after you create a new record.

DataTable Warning

It gives this warning because on creation of new record, readUsers is called which tries to instantiate DataTable object again.

Let’s fix the first problem. After creation of new record, we need to append it DataTable via its API. Before doing so, we need to return id of newly created record from model and controller. First return id from model using insert_id() method.

public function create() {
    $data = array(
        'name'  => $this->input->post( 'cName', true ),
        'email' => $this->input->post( 'cEmail', true )
    );
 
    $this->db->insert( 'users', $data );
    return $this->db->insert_id();
}

Then echo this insert id from controller’s create method.

public function create() {
    if( !empty( $_POST ) ) {
            echo $this->mUsers->create();
            //echo 'New user created successfully!';
    }
}

In submitHandler of validate(), replace readUsers() function call with fnAddData() method of DataTables as shown below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
success: function( response ) {
    $( '#msgDialog > p' ).html( 'New user created successfully!' );
    $( '#msgDialog' ).dialog( 'option', 'title', 'Success' ).dialog( 'open' );
 
    //clear all input fields in create form
    $( 'input', this ).val( '' );
 
    //refresh list of users by reading it
    //readUsers();
    dataTable.fnAddData([
        response,
        $( '#cName' ).val(),
        $( '#cEmail' ).val(),
        '<a class="updateBtn" href="' + updateUrl + '/' + response + '">Update</a> | <a class="deleteBtn" href="' + delUrl + '/' + response + '">Delete</a>'
    ]);
 
    //open Read tab
    $( '#tabs' ).tabs( 'select', 0 );
 
    //don't forget to fadeOut loading animation
    $( '#ajaxLoadAni' ).fadeOut( 'slow' );
}

On line 2, I replaced response with the string ‘New user created successfully!’ because now response contains the id of newly inserted record.

fnAddData() is on line 10. “response” is a parameter received in success handler of ajax call and it contains the id of record received from controller. The method fnAddData is used to insert a new row in DataTable. This method takes an object as a parameter containing values you want to display in a row. The first column in the table is id of record. So I placed “response” as the first property of object. Second is the name and third is email. Both of them are fethced from the Create form. Last column is Actions where Update and Delete links will go. The last property contain the links.

On line 21, loading animation is faded out. I added this line because now we are not calling readUsers function which hides loading animation in the end of success method of Ajax call.

Now the first problem is solved. But you may have noticed that after adding a new record, DataTable stopped displaying warning message. Second problem is also solved by solving the first one. But how? It’s because the readUsers function is now called only once. Still we must prevent reinstantiation of DataTable in readUsers function.

//apply dataTable to #records table and save its object in dataTable variable
if( typeof dataTable == 'undefined' )
    dataTable = $( '#records' ).dataTable({"bJQueryUI": true});

The only thing I added is the if condition before DataTable creation. In the if condition, datatype of dataTable table is checked to see if it’s undefined (no value is assigned to it), then call dataTable() and create new object of DataTable.

Few Recommendations

  1. If you have millions of records in database then never use this static method of pagination. We load all the records at a time in this method which is not good for performance. DataTable supports Ajax based loading of new records when next page is clicked. Better use that method.
  2. Always write the starting character of controller and model with uppercase because *inux based operating systems are not able to read/execute them. It was my fault that I wrote the name of model as mUsers. I must have written it as M_users. Same thing applies to controller.

comments powered by Disqus