Build Ajax Data Grids with CodeIgniter and jQuery

In this lesson, we will create a CodeIgniter library that allows us to generate data grids automatically for managing any database table. I'll explain each step required to create this class; so you'll likely learn some new OOP techniques/concepts in the process!

As a bonus, we'll proceed to write some jQuery code that will enable a user to update the data grid's content without having to wait for a page refresh.

Please Note...

This tutorial assumes that you have a modest understanding of the CodeIgniter and jQuery frameworks.

What is a Data Grid?

A datagrid is a table that displays the contents of a database or table along with sorting controls.

A datagrid is a table that displays the contents of a database or table along with sorting controls. In this tutorial, we will be tasked with providing this functionality, but also saving the user from waiting for the page to refresh each time an operation is performed. Thanks to jQuery, this will be a fairly simple task!

What about the users who don't have Javascript enabled? Don't worry, we'll compensate for them as well!

Step 1: Build a Data Grid Generator Class

We want to build a tool that will enable us to create datagrids dynamically for any database table that we have. This means the code is not tied up to any specific table structure, and, thus, is independent on the data itself. All the coder (the developer who uses our class) must know is the name of the table to be transformed into a grid and the primary key for that table. Here is the preface of the class that we will be developing for the most part of this tutorial:

The Datagrid Class could well be added to the application/library folder, but we are going to add it as a helper to the CodeIgniter framework. Why? Because loading libraries doesn't allow us to pass arguments to the class' constructor, thus loading it as a helper will solve the problem. This point will make more sense for you when we have finished writing the constructor.

We have much going on already; but don't worry, as I'll explain everything for you in the next paragraph.

The constructor takes two arguments: the first one being the name of the table in your database that you wish to display as a datagrid to the user; the second param is the name of the column serving as the primary key for that table (more on that later). Inside the constructor's body, we instantiate the CodeIgniter Object, the Database Object and the HTML Table class/library. All of these will be needed throughout a Datagrid object's lifetime and are already built into the CI framework. Notice that we also check if the primary key really exists in the given table, and, in case it does not, we throw an exception reporting the error. Now the $this->tbl_fields member variable will be available for later use, so we don't have to fetch the database again.

"We can use the command, $CI->db->list_fields($tbl_name) to fetch the names of all fields that a table has. However, for better performance, I recommend caching the results."

Method for Customizing Table Headings

This permits you to customize the headings of your data grid table - that is, with it, you can overwrite the original column names for certain table fields. It takes an associative array, like this: regdate => "Registration Date". Instead of just the technical "Regdate" as the column heading for that type of data, we have a more human-readable title in its place. The code responsible for applying the headings will be revealed shortly.

Method for Ignoring/Hiding Table Fields

ignoreFields receives an array containing the fields to be ignored when fetching data from the database. This is useful when we have tables with lots of fields, but we only want to hide a couple of them. This method is smart enough to track an attempt to ignore the primary key field and then skip that. This is so because the primary key cannot be ignored for technical reasons (you will see why shortly). Still, if you want to hide the primary key column from appearing in the UI, you can use the hidePkCol method:

public function hidePkCol($bool){
$this->hide_pk_col = (bool)$bool;
}

This method receives a boolean value to indicate if we want to hide the primary key column so it won't show up in the data grid. Sometimes, it's an ugly idea to display the pkey data, which is usually a numerical code without any meaning to the user.

Here we have a helper method; that's why it has the "private" modifier and is prefixed with an underline character (code convention). It will be used by the generate() method - explained shortly - in order to have the appropriate table fields selected and also the appropriate headings set to the table (generator) object. Notice the following line:

This is where we apply the customized headers or resort to the default ones if none is given. If the pk column is supposed to be hidden from display, then it's heading will be skipped. Also notice the following line:

The above command instructs the program to prepend a "Master" checkbox as the first heading of the table. That checkbox is different from other checkboxes in the grid in that it allows a user to check or uncheck all checkboxes in just one go. This toggling functionality will be implemented in a few moments with a simple jQuery code snippet.

The generate method, as its name suggests, is responsible for generating the data grid itself. You should call this method only after you have configured the object according to your needs. The first thing it does is call the $this->_selectFields() method to perform the actions we explained earlier. Now it has to fetch all rows from the database and then loop through them, adding checkboxes to the beginning of each row:

Inside the foreach loop on the generate method, if the $this->hide_pk_col flag is set to true, then we must unset the primary key entry in the $row array so it won't show up as a column when the $this->CI->tableobject processes all rows and generates the final html output. At this point, it is okay to remove the primary key, if necessary, because we no longer need that information. A

But what does the user do with the selected/checked rows? To answer this, I have prepared a few more methods. The first one enables us to create "action buttons" without having to know any technical details about how the grid system works internally:

Method for Adding Buttons to a Data Grid Form

Simply pass the name of the action as the first argument and a second argument to indicate the label for the generated button. A class attribute is automatically generated for that button so we can play around with it more easily when we are working with it in our JavaScript. But, how do we know if a certain action button has been pressed by the user? The answer can be found in the next method:

Yep! Another static method that helps us when we are dealing with forms. If any data grid has been submitted, this method will return the name of the action (or "operation") associated with that submit event. In addition, another handy tool for processing our datagrid forms is...

... which returns an array containing the selected ids so you can track which rows have been selected on the grid and then perform some action with them. As an example of what can be done with a selection of row ids, I have prepared another method - this one being an instance method, and not a static one, because it makes use of the object's instance resources in order to do its business:

If at least one checkbox was checked, the deletePostSelection() method will generate and execute an SQL statement like the following (suppose $tbl_name='my_table' and $pk_col='id'):

DELETE FROM my_table WHERE id IN (1,5,7,3,etc...)

...which will effectively remove the selected rows from the persistent layer. There could be more operations you could add to a data grid, but that will depend on the specifics of your project. As a tip, you could extend this class to, say, InboxDatagrid, so, beyond the deletePostSelection method, it could include extra operations, such as moveSelectedMessagesTo($place), etc...

Putting everything together

Now, if you have followed this tutorial step by step, you should have ended up with something similar to the following:

An instance of this class is created and passed as a reference to the $this->Datagrid member. Notice that we will be fetching data from a table called "users" whose primary key is the "id" column; then, on the index method we take the following steps: configure the Datagrid object, render it inside a form with a delete button added to it and see if everything works as expected:

Question: What happens when the form is sent?

Answer: The "Test::proc()" method takes care of processing the form and choosing the right operation to apply against the ids that were selected by the form's sender. It also takes care of AJAX requests, so it will echo a JSON object back to the client. This AJAX-aware feature will come in handy when jQuery comes into action, which is right now!

"It's always a smart idea to create web applications which compensates for when JavaScript/AJAX is unavailable. This way, some users will have a richer and faster experience, while those without JavaScript enabled will still be able to use the application normally."

Step 3: Implementing Ajax (jQuery to the Rescue!)

When the user clicks the button (or any other action button), we would like, perhaps, to prevent the page from reloading and having to generate everything again; this could make the user of our application fall asleep! Circumventing this problem will not be a difficult task if we stick to the jQuery library. Since this is not a "beginners" tutorial, I will not go through all the details related to how to get the library, how to include it on the page, etc. You're expected to know these steps on your own.

Create a folder, named "js", add the jQuery library within, and create a view file, named users.php. Open this new file, and add:

Now, please, create a "datagrid.js" file, put it on the "js" directory, and start with this code:

$(function(){
// cool stuff here...
})

Inside this closure, we will write code that will be tasked with controlling certain submit events once the page has completely loaded. The first thing we need to do is track when a user clicks a submit button on the data grid form, and then send that data to be processed on the server.

Alternatively, we could have started with something like: $('.dg_form').submit(function(e){...}). However, since I want to track which button has been pressed and extract the name of the chosen action based on it, I prefer binding an event handler to the submit button itself and then go my way up the hierarchy of nodes to find the form that the pressed button belongs to:

// finds the form
var $form = $(this).parents('form');
// extracts the name of the action
var action_name = $(this).attr('class').replace("dg_action_","");

Next, we add a hidden input element inside the form element to indicate which action is being sent:

This is necessary because function doesn't consider the submit button to be a valid form entry. So we must have that hack in place when serializing the form data.

action_control.remove();

"Don't forget: the function ignores the submit button, dismissing it as just another piece of markup junk!"

Sending Form Data to the Server

Next, we proceed to get the action attribute from the form element and append the string "/ajax" to that url, so the method will know that this is, in fact, an AJAX request. Following that, we use the jQuery.post function to send the data to be processed by the appropriate controller, server-side, and then intercept the response event with a registered callback/closure:

Notice that we are asking the response to be encoded as "json" since we are passing that string as the fourth argument of the $.post function. The contents of the callback dealing with the server response should be rather simple to grasp; it determines if there is an error, and, if so, alerts it. Otherwise, it will indicate that the action was successfully processed (in this case, if it is a "" action, we remove the rows related to the ids that were selected by the user).

Step 4: Check All or Nothing!

The only thing that is missing now is the toggle functionality that I promised earlier. We must register a callback function for when the "Master" checkbox - which has a class attribute set to "dg_check_toggler" - is clicked. Add the following code snippet after the previous one: