Ajax user table management with codeigniter and jquery

GFX9.COM share Ajax user table management with codeigniter and jquery, you can download now.



Learn how to use CodeIgniter and the jQuery library to create a slick, AJAX-utilizing management page for a typical "Users" table, and discover some of the awesome features of jQuery UI.

To work along with this article, you're expected to be comfortable with writing PHP, and ideally, slightly familiar with the CodeIgniter framework.


Introduction

This tutorial will guide you through the process of creating a slick, AJAX-utilizing management page for a typical "Users" table in a MySQL database. We will cover a range of topics, in both CodeIgniter and jQuery, including creating and using Models, Controllers and Views, the Form Validation class, and jQuery UI.

I have tried to write this tutorial to mimic a process as close to real development as possible, but of course with the added benefit of hindsight. Hopefully, the build will feel natural, as you follow along and create your own application.


Step 1 - Create a new CodeIgniter Project

As this tutorial is all about CodeIgniter, we'll kick things off by starting with a fresh CI install. If you have never used CodeIgniter before, head over to their website to download the latest version, and find out how you can start a new project in literally minutes.

Once you're ready with your fresh CI install, you'll need to configure your new application with your existing MySQL database. To do this, find the database.php file in application/config/ and enter the relevant database host, username, password and name in the following lines:

 
$db['default']['hostname'] = 'localhost';   /* The MySQL database host  */ 
$db['default']['username'] = 'root';        /* Database username        */       
$db['default']['password'] = 'root';        /* Database password        */ 
$db['default']['database'] = 'Tutorial';    /* Database name        */

Autoloading the Database library

Your application should now be ready to connect to the database. However, CodeIgniter handles all database interactions through the Database library, which must be 'loaded in' before any interactions are initiated. To save us a little bit of time and code, we can tell CodeIgniter to load in the class automatically for us, on every page load.

Find the autoload.php file in application/config/ and add the 'database' class to the list of libraries to autoload.

 
$autoload['libraries'] = array('database');

Step 2 - Setup the table in the database

Next we shall create the table that holds our user's information in our MySQL database, and enter in a couple of records. We'll keep the table structure short and simple for this application.

Execute the following code to create the Users table in your pre-existing database.

 
CREATE TABLE  `Users` ( 
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , 
`first_name` VARCHAR( 20 ) NOT NULL , 
`last_name` VARCHAR( 20 ) NOT NULL , 
`dob` DATE NOT NULL , 
`gender` VARCHAR( 1 ) NOT NULL , 
`email_address` VARCHAR( 50 ) NOT NULL 
) ENGINE = MYISAM ;



Your table should now have this structure.

And let's enter some information into the table, for fictional users John and Alice.

 
 
INSERT INTO  `Users` ( 
`id` , 
`first_name` , 
`last_name` , 
`dob` , 
`gender` , 
`email_address` 
) 
VALUES ( 
NULL ,  'John',  'Smith',  '1992-05-08',  'M',  'johnsmith@example.com' 
), ( 
NULL ,  'Alice',  'Bond',  '1990-07-21',  'F',  'alicebond@example.com' 
);

The database is now setup and ready to be interacted with by our application.


Step 3 - Creating the Model

Now we're going to get our hands dirty and start writing some code.

The various interactions that we want to be able to do with our database are going to be stored in a Model, which is a special type of PHP file in the CodeIgniter framework.

What is a Model?

As this is not an introduction to CodeIgniter, or MVC architecture in general, I won't dwell too much on what a Model actually is, but the CodeIgniter user guide does a pretty good job of explaining it.

Models are PHP classes that are designed to work with information in your database. For example, let's say you use CodeIgniter to manage a blog. You might have a model class that contains functions to insert, update, and retrieve your blog data.

We can extrapolate this to our application, as we will require functions, or methods to insert, update, delete and retrieve user information.

The methods we will in write in our Model will perform the following actions:

  • Get the full list of users (or just one) and their details
  • Add a new user to the table
  • Update an existing user's record
  • Delete a user from the table

We will also be using the Active record class/pattern for all our database queries.

Beyond simplicity, a major benefit to using the Active Record features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.

Create a new file in application/models/ called users_model.php and write the follwing code inside:

 
db->select('id, first_name, last_name, dob, gender, email_address'); 
            $this->db->from('Users'); 
            if (!is_null($id)) $this->db->where('id', $id); 
            $this->db->order_by('id', 'desc'); 
            return $this->db->get()->result(); 
        } 
         
        /** 
         * @name        add 
         *  
         * @desc        Add a user into the table 
         * 
         * @param       data    (array)  
        */ 
         
        function add($data)  
        { 
            $this->db->insert('Users', $data); 
            return $this->db->insert_id(); 
        } 
         
        /** 
         * @name        update 
         *  
         * @desc        Update a user record 
         * 
         * @param       id      (int) 
         * @param       data    (array)  
        */ 
         
        function update($data, $id)  
        { 
            $this->db->where('id', $id); 
            $this->db->update('Users', $data); 
            return $this->db->affected_rows(); 
        } 
         
        /** 
         * @name        delete 
         *  
         * @desc        Delete a user record 
         * 
         * @param       id      (int)    
        */ 
         
        function delete($id)  
        { 
            $this->db->where('id', $id); 
            $this->db->delete('Users'); 
            return $this->db->affected_rows(); 
        }    
     
    } 
     
    /* End of file users_model.php */ 
    /* Location: ./application/models/users_model.php */

Let's break down each method to see what it does

Retrieve the list of users

 
function get($id = null)  
{ 
    $this->db->select('id, first_name, last_name, dob, gender, email_address'); 
    $this->db->from('Users'); 
    if (!is_null($id)) $this->db->where('id', $id); 
    $this->db->order_by('id', 'desc'); 
    return $this->db->get()->result(); 
}

In the snippet above, the method get() is defined and selects the id, first_name, last_name, dob, gender and email_address fields from the Users table, ordered by descending id.

We've also included room for the optional parameter $id. If $id is supplied to the method, only the record with that id is returned. This will be used later on to grab a record after it is added.

An array of objects, one for each user matched in the table, is returned.

Add a user

 
function add($data)  
{ 
    $this->db->insert('Users', $data); 
    return $this->db->insert_id(); 
}

In this next snippet of code, the add() method is defined, which takes an associative array $data as a parameter, and runs a query to add the values to the table. The keys of the $data array will be the field names of the User table, with the corresponding values being the data inserted.

For example, the $data array for our user, Alice Bond would look like this:

 
 
$data = array( 
 
    'first_name'    =>  'Alice', 
    'last_name'     =>  'Bond', 
    'dob'           =>  '1990-07-21', 
    'gender'        =>  'F', 
    'email_address' =>  'alicebond@example.com' 
     
);

The id of the new user record is returned.

Update a user

 
function update($data, $id)  
{ 
    $this->db->where('id', $id); 
    $this->db->update('Users', $data); 
    return $this->db->affected_rows(); 
}

Here the update() method is defined, which again takes an associative array $data of the same form as in the add() method, as well as another parameter, $id. The code then runs a query to update the fields specified in $data with the new values, on the row with field id equal to $id

The number of affected rows is returned, which on a successful edit in this situation will be 1. N.B If the new values in $data are no different to the existing values for a record, no update will take place on the table and the method will return 0.

Remove a user

 
function delete($id)  
{ 
    $this->db->where('id', $id); 
    $this->db->delete('Users'); 
    return $this->db->affected_rows(); 
}

Finally, the remove() method is defined, which this time only takes the $id parameter. The code runs a query to delete the record of a user from the table, with id equal to $id.

Again, the number of affected rows is returned, which should also be 1 in our particular application.

The methods in this Model can now be used in other parts of our application, namely the Controllers.


Step 4 - Creating the main Controller

Now that we've created our Model, it's time to write our first Controller.

What is a Controller?

Again, I don't want to dwell too much on what a actually Controller is, so here's how CI put it:

A Controller is simply a class file that is named in a way that can be associated with a URI.

In this controller, we will retrieve the records of users from the database via our get() method in the Users Model, and eventually display this information by loading a view (more on that later).

So to begin, create a file called manage.php in application/controllers/ and write the following code:

 
load->model('Users_model', 'users'); 
            // Load the Users Model with our methods of interacting with the database. 
             
            $users = $this->users->get(); 
            // Get the array of user records from the database. 
        } 
         
    } 
     
    /* End of file manage.php */ 
    /* Location: ./application/controllers/manage.php */

The above code defines the method index(), in which our newly created Users Model is loaded in (and given the shorter object reference name of 'users') and the list of user records is retrieved and assigned to the variable $users via the get() method in the Users Model.

This controller can be run by accessing http://yoursite.com/path/to/ci-install/index.php/manage, but as nothing is being outputted yet, you will see a blank page.


Step 5 - Creating the View

Now we want to display the list of our users on screen, and this is accomplished by creating and loading a View.

A view is simply a web page, or a page fragment, like a header, footer, sidebar, etc

In our view file, we will store the HTML (and a little bit of PHP) code that displays our user list on screen.

Before we begin creating our view, it's a good idea to create a few folders to store any CSS or JavaScript files that we may use. Create a new folder named assets in the root of your CI installation, and inside this two more new folders named css and js.


The HTML

We are now ready to write the HTML for our page. The page will contain two things:

  • A table to hold our user records
  • A form to add and update user records

Create a new file called manage.php in application/views/ and write the following code:

 
 
 
     
         
        User Management with CodeIgniter and jQuery 
         
     
     
         
         
        
load->view('table_rows.php'); ?>
User Management
First Name Last Name DoB Gender Email address
users in the database

Note: In the above code, we have loaded in another view file with the following code:

 
load->view('table_rows.php'); ?>

This second view file will hold the PHP/HTML code to loop through the $users array, and output the user records in the table rows.

Why not just place the table row code directly inside the view?

The table row HTML/PHP code could just as easily been placed inside the manage.php view file directly, but there is a benefit to separating it into it's own file.

When we ultimately add a new user via AJAX, we'd like the jQuery to add a row in to the table for us with our new user's data. To keep the design consistent, we can use this table_rows.php view again to retrieve the HTML for the new record's isolated row.

The table row HTML

So let's write the view file for the table rows. Create a new file named table_rows.php in application/views/ and write the following:

 
 
     
     
 
    > 
        first_name;?> 
        last_name;?> 
        dob;?> 
         
            gender == 'M')  
            { 
                echo 'Male'; 
            } 
            else if ($user->gender == 'F')  
            { 
                echo 'Female'; 
            } 
            ?> 
         
        email_address;?> 
         
            

In the above code, the $user array is looped through, creating a new table row for each user, and echoing the data in each table cell.

To make the gender text for each user more "human" than the single character entry we have in the database, a small if/else condtion converts the value to the full word conterpart, "Male" or "Female".

 
gender == 'M')  
    { 
        echo 'Male'; 
    } 
    else if ($user->gender == 'F')  
    { 
        echo 'Female'; 
    } 
?>

Finally, maybe one more thing to explain is the use of the $count variable, which is used to add a CSS class to every other row, to create the Zebra effect.

 
 
// echo 'class="alternate" when $count is odd

The CSS

In the HTML code inside the manage.php view, the CSS file style.css is linked in, so let's create this in our assets/css/ folder, and write the following code in:

 
/* Set the stage */ 
body  
{ 
    background:#222; 
    font-family:Arial, sans-serif; 
    text-align:center; 
    font-size:15px; 
} 
 
/* Table styles */ 
#ajax_table  
{ 
    margin:50px auto 20px auto; 
    border:1px solid #111; 
    text-align:left; 
} 
/* Caption */ 
#ajax_table caption  
{ 
    text-align:left; 
    padding:20px; 
    font-size:22px; 
    color:#fff; 
    text-shadow:#111 0px 1px 1px; 
} 
 
/* Headings & Cells */ 
#ajax_table thead tr th, #ajax_table tbody tr td  
{ 
    padding:10px 10px 10px 20px; 
} 
 
/* Headings */ 
#ajax_table thead tr th  
{ 
    background:#DEDEDE; 
    border-bottom:1px solid #CCC; 
    color:#666; 
    font-size:13px; 
    text-shadow:#FFF 0px 1px 1px; 
} 
/* Cells */ 
#ajax_table tbody tr td  
{ 
    background:#CEEAF9; 
    border-top:1px solid #fff; 
    border-bottom:1px solid #AED7EC; 
    color:#294A64; 
    text-shadow:#fff 0px 1px 1px; 
} 
/* Alternate cells */ 
#ajax_table tbody tr.alternate td  
{ 
    background:#fff; 
    color:#555; 
    border-bottom:none; 
    text-shadow:none; 
} 
/* Footer */ 
#ajax_table tfoot tr td  
{ 
    background:#DEDEDE; 
    border-top:1px solid #fff; 
    border-bottom:1px solid #CCC; 
    padding:5px 10px; 
    text-align:right; 
    font-size:12px; 
    color:#666; 
    text-shadow:#FFF 0px 1px 1px; 
    font-style:italic; 
} 
 
/* Button list */ 
ul.buttons  
{ 
    float:right; 
    margin:0; 
    padding:0; 
    list-style:none; 
} 
    ul.buttons li  
    { 
        float:left; 
        padding:0; 
        margin:0 5px; 
    } 
     
/* Buttons */ 
button  
{ 
    font-size:12px!important; 
    outline:none; 
} 
 
/* User Form */ 
#user_form  
{ 
    display:none; 
    text-align:left; 
    padding-top:20px; 
    font-size:13px; 
} 
/* Labels */ 
#user_form label  
{ 
    float:left; 
    width:120px; 
    margin:0 0 10px 0; 
    height:22px; 
    line-height:22px; 
} 
/* Inputs */ 
#user_form input[type=text]  
{ 
    float:left; 
    width:240px; 
    padding:5px; 
    height:10px; 
    margin:0 0 10px 0; 
    border:1px solid #999; 
} 
/* Selects */ 
#user_form select  
{ 
    float:left; 
    margin:0 0 10px 0; 
} 
 
/* Form feedback */ 
p.feedback  
{ 
    display:none; 
    margin:0 0 20px 0!important; 
    padding:5px 10px; 
    border-radius:5px; 
    text-align:center; 
} 
/* Error */ 
p.error  
{ 
    background:#E97979; 
    color:#752121; 
    border:1px solid #D13E3E; 
} 
/* Notify */ 
p.notify  
{ 
    background:#FBD480; 
    color:#5D4616; 
    border:1px solid #DDBB45; 
} 
 
/* Notification paragraph */ 
p#notification  
{ 
    position:absolute; 
    top:0; 
    left:0; 
    width:100%; 
    display:none; 
    text-align:center; 
    padding:15px 0; 
    margin:0; 
    color:#fff; 
} 
 
p#notification.error  
{ 
    background:red; 
} 
 
p#notification.success  
{ 
    background:green; 
} 
 
/* Generic */ 
.float-right  
{ 
    float:right; 
} 
 
.clear  
{ 
    clear:both; 
}

Step 6 - Linking the Controller and View

We have the controller and the view, and we are now ready to link them together, so that the view is displayed when the controller is accessed.

To do this, we need to do load in the view from the controller, and pass the $users variable to the view.

Open up manage.php in application/controllers/ again, and add in this extra code:

 
load->model('Users_model', 'users'); 
            // Load the Users Model with our methods of interacting with the database. 
             
            $users = $this->users->get(); 
            // Get the array of user records from the database. 
             
            /* Begin extra code */ 
             
            $tpl_data = array( 
             
                'users'     =>  $users 
                 
            ); 
            // Create an array of variables that need to be accessible in the View 
             
            $this->load->view('manage.php', $tpl_data); 
            // Load the view 
             
            /* End extra code */ 
        } 
         
    } 
     
    /* End of file manage.php */ 
    /* Location: ./application/controllers/manage.php */

In the additions to the code, an associative array $tpl_data is defined, and holds our array of users, $users, under the key 'users'.

The manage.php view is then loaded, and the variables in $tpl_data are passed to the view, so that each value in $tpl_data can be accessed in the view.

Visit the page again, at http://yoursite.com/path/to/ci-install/index.php/manage and you should now see the user table, with John and Alice's records being displayed.


The Add, Update and Remove buttons look a little ugly right now, but we shall fix them up later.

You should also notice that our form is not displaying on the page. This is because in our CSS we have set the form to display:none;. Later on, we will be applying the jQuery UI dialog widget to the form, so we can forget about it for now.


Step 7 - Creating the AJAX Controller

We need to create another seperate controller, to process the AJAX requests we will be making later on from the view.

In this controller, we shall be making use of the other methods we defined in our Users Model, to add, edit and remove users.

The Form Validation Class

As we are going to be sending POST data to this controller, we will also make use of the Form Validation class that comes with CodeIgniter. This library provides an easy way to validate posted data by setting different validation 'rules' for each item. The full rule reference can be found here.

You shall see how the library is used to validate our data before any database interactions are attempted.

JSON output

As this controller will be exclusively accessed by our jQuery AJAX requests, and not directly by a user, we require any output from the controller to be in a format that jQuery can easily read and parse.

All output will be in the form of PHP arrays encoded into JSON strings. This means we can output multiple pieces of data from the controller, such as a success/fail status and a message, to be parsed by our jQuery code. Don't worry if you are not aware of what JSON is, or how to parse it as this will become clear later on.

Just like our main controller, create a new file called ajax.php in applications/controllers/ and begin by writing the following code within:

 
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 
 
    class Ajax extends CI_Controller  
    { 
        /** 
         * @name        __construct 
         *  
         * @desc        Load in the model, and validation class 
         * 
        */ 
 
        function __construct()  
        { 
            parent::__construct(); 
            // Call the parent constructor 
             
            $this->load->model('Users_model', 'users'); 
            // Load in the Users Model 
             
            $this->load->library('form_validation'); 
            // Load in the Form validation library 
        }

Here, we have defined the Ajax Controller, and defined the __construct() constructor method, which is a special method which is automatically called as soon as the Ajax controller is initated. In this method, we load in the Users model once again, and the form validation library also.

Inside the constructor method, the parent class' __construct() is called. It is imperitive that this is called, as the base class, CI_Controller's constructor method will not be implicty called if the constructor method is defined inside the child class.

Next, we will write the method to receive POST data, and add this to the database.

 
         
        /** 
         * @name        add 
         *  
         * @desc        Add a user 
         * 
        */ 
         
        function add()  
        { 
            $this->form_validation->set_rules('first_name',     'First Name',       'required|max_length[20]'); 
            $this->form_validation->set_rules('last_name',      'Last Name',        'required|max_length[20]'); 
            $this->form_validation->set_rules('dob',            'DoB',              'required|exact_length[10]'); 
            $this->form_validation->set_rules('gender',         'Gender',           'required|exact_length[1]'); 
            $this->form_validation->set_rules('email_address',  'Email address',    'required|valid_email|max_length[50]'); 
            // Set the form validation rules 
             
            if ($this->form_validation->run() != FALSE)  
            { 
                // Passed the form validation 
                 
                $user = array( 
                 
                    'first_name'    =>  $this->input->post('first_name'),  
                    'last_name'     =>  $this->input->post('last_name'),  
                    'dob'           =>  $this->input->post('dob'),  
                    'gender'        =>  $this->input->post('gender'),  
                    'email_address' =>  $this->input->post('email_address') 
                     
                ); 
                // Create the data array for the new user 
                 
                if ($add = $this->users->add($user))  
                { 
                    // The user was added to the database 
                     
                    $data = array( 
                        'users' => $this->users->get($add) 
                    ); 
                    // Fill the $data['users'] with the array contining the record of the user that's just been added 
                     
                    $row_html = $this->load->view('table_rows.php', $data, true); 
                    // Assign the view html of the table row for the new user to $row_html with third TRUE parameter 
                     
                    $return = array( 
                     
                        'status'    =>      'success',  
                        'message'   =>      $this->input->post('first_name').' '.$this->input->post('last_name').' has been  
                                            added!',     
                        'html'      =>      $row_html 
                     
                    ); 
                     
                    // set the output status, message and table row html 
                     
                    echo json_encode($return); 
                    // print out the JSON encoded output 
                     
                } 
                else 
                { 
                    $return = array( 
                     
                        'status'    =>      'failed',  
                        'message'   =>      'Failed to add to the DB' 
                         
                    ); 
                    echo json_encode($return); 
                    // return the error message 
                } 
                 
            } 
            else 
            { 
                $return = array( 
                     
                    'status'    =>      'failed',  
                    'message'   =>      validation_errors('', '<br>') 
                     
                ); 
                echo json_encode($return); 
                // return the error message 
                 
            } 
             
        }

In the above snippet, the validation rules are set for the POST data, and then a check is done to see if the data validates. If the data does validate, an attempt is made to add the user, using the add method in the Users Model.

If the user is successfully added to the database, the HTML of the new user's table row is loaded in using the get() method in the Users Model, and the table_rows.php view, and three things are outputted in the JSON string:

  • status - "success"
  • message - "Name has been added!"
  • html - the HTML of the table row for the user

This output will be parsed by the jQuery later on when making AJAX requests to determine if the insert was successful, and to display the messages back to the user.

If the user is not added to the database, or the validation is failed, an status of "failed", and an error message is outputted.

I must quickly mention my use of the validation_errors() function. This is a function in the CodeIgniter system that works with the Form Validation class.

This function will return any error messages sent back by the validator. If there are no messages it returns an empty string.

The two parameters for the function are the start and end delimiters, that each validation error is wrapped in.

Now we shall write the method to update a user record:

 
         
        /** 
         * @name        update 
         *  
         * @desc        Update a user record 
         * 
        */ 
         
        function update()  
        { 
            $this->form_validation->set_rules('id',             'User ID',          'required|is_natural_no_zero'); 
            $this->form_validation->set_rules('first_name',     'First Name',       'required|max_length[20]'); 
            $this->form_validation->set_rules('last_name',      'Last Name',        'required|max_length[20]'); 
            $this->form_validation->set_rules('dob',            'DoB',              'required|exact_length[10]'); 
            $this->form_validation->set_rules('gender',         'Gender',           'required|exact_length[1]'); 
            $this->form_validation->set_rules('email_address',  'Email address',    'required|valid_email|max_length[50]'); 
            // Set the form validation rules 
             
            if ($this->form_validation->run() != FALSE)  
            { 
                // Passed the form validation 
                 
                $user = array( 
                 
                    'first_name'    =>  $this->input->post('first_name'),  
                    'last_name'     =>  $this->input->post('last_name'),  
                    'dob'           =>  $this->input->post('dob'),  
                    'gender'        =>  $this->input->post('gender'),  
                    'email_address' =>  $this->input->post('email_address') 
                     
                ); 
                // Create the data array 
                 
                if ($this->users->update($user, $this->input->post('id')) !== FALSE)  
                { 
                    // The user was updated 
                     
                    $data = array( 
                        'users' => $this->users->get($this->input->post('id')) 
                    ); 
                    // Fill the $data['users'] with the array contining the record of the user that's just been updated 
                     
                    $row_html = $this->load->view('table_rows.php', $data, true); 
                    // Assign the view html of the table row for the new user details to $row_html with third TRUE parameter 
                                         
                    $return = array( 
                     
                        'status'    =>      'success',  
                        'message'   =>      $this->input->post('first_name').' '.$this->input->post('last_name').' has been  
                                            updated!',   
                        'html'      =>      $row_html 
                     
                    ); 
                    // set the output status, message and table row html 
                     
                    echo json_encode($return); 
                    // print out the JSON encoded success/user details 
                     
                } 
                else 
                { 
                    $return = array( 
                     
                        'status'    =>      'failed',  
                        'message'   =>      'Failed to update User ID #'.$this->input->post('id') 
                         
                    ); 
                    echo json_encode($return); 
                    // return the error message 
                } 
                 
            } 
            else 
            { 
                $return = array( 
                     
                    'status'    =>      'failed',  
                    'message'   =>      validation_errors('', '<br>') 
                     
                ); 
                echo json_encode($return); 
                // return the error message 
                 
            } 
             
        }

This code is, perhaps not suprisingly, very similar to the add method, and if you think back to the analog methods in the Users Model, they were very similar too.

The only real differences between this and the previous method, is the inclusion of POST data 'id' in the form validation rules, and calling the update method instead of add.

I'd also like to point out that when checking if the update was successful, we use the comparison operator "!==". This will only return false if the method returns FALSE (not just 0), so updates supplied with identical data will return successful.

Finally, the last method we will write in this controller, is used to remove a user record:

 
         
        /** 
         * @name        delete 
         *  
         * @desc        Delete a user 
         * 
        */ 
 
        function delete()  
        { 
            $this->form_validation->set_rules('id', 'User ID', 'required|is_natural_no_zero'); 
            // Make sure the User ID is posted, and a non zero natural number (1, 2, 3, ....) 
             
            if ($this->form_validation->run() != FALSE)  
            { 
                // Validation passed, try to delete the user 
                 
                if ($this->users->delete($this->input->post('id')))  
                { 
                    // The user was successfully removed from the table 
                     
                    $return = array( 
                     
                        'status'    =>      'success',  
                        'message'   =>      'The user has been deleted!' 
                         
                    ); 
                     
                    echo json_encode($return); 
                     
                    // print out a JSON encoded success message 
                } 
                else 
                { 
                    // The delete failed 
                     
                    $return = array( 
                     
                        'status'    =>      'failed',  
                        'message'   =>      'Failed to delete User ID #'.$this->input->post('id') 
                         
                    ); 
                     
                    echo json_encode($return); 
                    // print out a JSON encoded error message 
                     
                } 
                 
            } 
            else 
            { 
                // Validation failed or no post data 
                 
                $return = array( 
                     
                    'status'    =>      'failed',  
                    'message'   =>      validation_errors('', '<br>') 
                     
                ); 
                 
                echo json_encode($return); 
                // print out a JSON encoded error message 
                 
            } 
        }

Much like the previous methods, we first set the form validation rules. As we only need the id of the record we wish to delete, this is the only data we need validate. Again, the data is validated, and if it passes an attempt to delete the record is made, via the delete method in the Users Model.

Again, a JSON string is outputted detailing whether the removal was successful, and a message.

We now of course must close the class with a final right brace.

 
         
    } 
     
    /* End of file ajax.php */ 
    /* Location: ./application/controllers/ajax.php */

Step 8 - Using jQuery to POST to the AJAX controller

Our final step is to right the jQuery code that will communicate with our AJAX Controller.

Using jQuery UI


There are a handful of things that we would like on our page, that will make it much more pleasent to look at, and easier to
use. For example, we want to make our ugly buttons look nice, and perhaps use a datepicker to let the user easily select a Date of Birth when adding or updating a user.

This is where the jQuery UI comes in.

jQuery UI is an open source library of interface components - interactions, full-featured widgets, and animation effects - based on the stellar jQuery javascript library.

Once you have downloaded the package and unzipped the file, copy the folder and files in the css and js folders into the corresponding folders inside the assets folder in the root of your CI installation.


Now we must go back to our manage.php view file, and link in the CSS and JavaScript files we have just added to our assets.

Add the following code inside the tag in application/views/manage.php:

 
 
 
 

In the above code, we have included the CSS and Javascript files needed for the UI, and an extra JavaScript file named ajax-table.js that we are yet to create. This file will hold all of our jQuery code.

We are now ready to use the jQuery UI!

Styling the buttons

Okay, so let's use the jQuery UI straight away by making our buttons a little less ugly.

Create a file called ajax-table.js in assets/js/ and write the following code:

 
$(document).ready(function() 
{ 
    button_bind(); 
}); 
 
function button_bind()  
{ 
    // Set the buttons icons 
     
    $('button.add').button( 
    { 
        icons:  
        { 
            primary: 'ui-icon-plusthick' 
        } 
    }); 
    // Give all .add buttons a nice UI icon 
     
    $('button.edit').button( 
    { 
        icons:  
        { 
            primary: 'ui-icon-pencil' 
        }, 
        text: false 
    }); 
    // Give all .edit buttons a nice UI icon 
     
    $('button.remove').button( 
    { 
        icons:  
        { 
            primary: 'ui-icon-closethick' 
        }, 
        text: false 
    }); 
    // Give all .remove buttons a nice UI icon 
}

In the code above, the function button_bind() is executed when the DOM is ready.

The button_bind() function itself uses the jQuery UI to change the appearance of certain button elements on the page, via the button() method.

If you load http://yoursite.com/path/to/ci-install/index.php/manage again, you will now see the ugly buttons have been replaced by something much more pleasent.


We also wanted to use the jQuery UI to add a datepicker to our Date of Birth input box in our form. Add the following code underneath the button_bind(); call in the ready() function:

 
$('input.date').datepicker({ 
             
    dateFormat: 'yy-mm-dd', 
    changeMonth: true, 
    changeYear: true,  
    yearRange: '1910:2012' 
     
});

This applies the datepicker to all input elements with CSS class 'date', and sets the date format to the one used in the Users table.

Before we write any functions to manipulate our user data with jQuery, we should store some jQuery objects that we will be using frequently throughout our different functions in a few variables.

We will want to store the jQuery objects of our form, table body and the span element in our table footer which holds the number of users in the database, as these objects will be referenced and changed when manipulating our user data.

Write the following code at the very top of ajax-table.js:

 
var form;                   // jQuery object of the form used to add/update 
var tbody;                  // jQuery object of our table's tbody element 
var count_span;             // jQuery object of the span inside the table footer, which contans the no. of records

And the following code inside the ready() function, before the button_bind() call:

 
form        = $('#user_form'); 
tbody       = $('#ajax_table tbody'); 
count_span  = $('#ajax_table tfoot tr td span');

The jQuery UI Dialog

A dialog is a floating window that contains a title bar and a content area. The dialog window can be moved, resized and closed with the 'x' icon by default.

We shall be using the jQuery UI dialog to display (and hide) the form used to add and update user records. To achieve this we must apply the dialog() method to our form object.

Add the following code underneath the last snippet of code:

 
form.dialog({ 
     
    width:400,          // set the width of the dialog to 400px 
    autoOpen:false,     // don't want it to open automatically 
    resizable:false,    // set it to not resizable 
    modal:true,         // use a modal overlay background 
     
    close: function()   // onClose 
    { 
        $('p.feedback', form).html('').hide(); 
        // clear & hide the feedback msg inside the form 
        $('input[type="text"], input[type="hidden"]', form).val(''); 
        // clear the input values on form 
    } 
     
});

Whilst applying the dialog function on our form object, we have specified a function that should execute whenever the dialog is closed. In this function, the feedback

element and all input elements in the form have their HTML/values cleared. This means that every time the form is "popped open", the paragraph or input elements will not contain any previous data in them.

Displaying the messages

We want a way to display the messages outputted by the AJAX Controller on the screen from our jQuery code, and for these messages to have a different style, depending on whether the message is a success or error message.

So, we need to write a function that will take a message, and display it on the screen, and give the element a supplied CSS class.

Write the following code underneath the button_bind() function:

 
function showMessage(message, p_class)  
{ 
    if (!$('p#notification').length)  
    { 
        $('body').prepend('<p id="notification"></p>'); 
    } 
 
    var paragraph = $('p#notification'); 
    paragraph.hide(); 
    paragraph.removeClass(); 
    // remove all classes from the <p> 
    paragraph.addClass(p_class); 
    // add the class supplied 
    paragraph.html(message); 
    // change the text inside 
    paragraph.fadeIn('fast', function()  
    { 
        paragraph.delay(3000).fadeOut(); 
        // fade out again after 3 seconds 
     
    }); 
    // fade in the paragraph again 
}

The small function checks to see if a paragraph with id="notification" exists, and creates one if not. It then applies the class specified to the element, changes the content inside and displays to the user. The message then fades out after 3 seconds.

We will use this function in our other methods to inform the user of successful or failed changes.

Writing the add, update and remove functions

Our buttons may look nice now, but they don't actually do anything yet. We need to tell jQuery to execute some functions whenever any of the three button types are clicked.

Edit the button_bind() function to look like this:

 
function button_bind()  
{ 
    // Set the buttons icons and click functions 
     
    $('button.add').button( 
    { 
        icons:  
        { 
            primary: 'ui-icon-plusthick' 
        } 
    }). 
    click(initAdd); 
    // Give all .add buttons a nice UI icon and trigger initAdd() on click 
     
    $('button.edit').button( 
    { 
        icons:  
        { 
            primary: 'ui-icon-pencil' 
        }, 
        text: false 
    }) 
    .click(initUpdate); 
    // Give all .edit buttons a nice UI icon and trigger initUpdate() on click 
     
    $('button.remove').button( 
    { 
        icons:  
        { 
            primary: 'ui-icon-closethick' 
        }, 
        text: false 
    }) 
    .click(initRemove); 
    // Give all .remove buttons a nice UI icon and trigger initRemove() on click 
}

All we have done here is set a function to execute whenever the buttons are pressed using the click() function.

We must now write the functions we have specified to be executed, namely, initAdd(), initUpdate() and initRemove().

initAdd()

First we shall create the function that executes when the 'Add User' button is clicked.

Underneath the showMessage() function, write the following code:

 
function initAdd() 
{ 
    form.dialog('option', 'title', 'Add User'); 
    // Set the title of the dialog to 'Add User' 
     
    // Now set the buttons and their functions for the dialog 
    form.dialog({ 
    buttons:  
    { 
        'Cancel': function()        // create a 'Cancel' button to close the dialog 
        { 
            form.dialog('close');    
        }, 
         
        'Add User': function()      // create the 'Add User' submit button 
        { 
            $('p.feedback', form).removeClass('success, error').addClass('notify'). 
            html('Sending details to server...').show(); 
            // notify the user the details are being sent to the server 
             
            var data = {}; 
            var inputs = $('input[type="text"], input[type="hidden"], select', form); 
             
            inputs.each(function(){ 
                var el = $(this); 
                data[el.attr('name')] = el.val(); 
            }); 
            // collect the form data form inputs and select, store in an object 'data' 
             
            $.ajax({ 
            type:       'POST', 
            url:        'ajax/add', 
            data:       data, 
            dataType:   'json',  
            success:    function(json)  
            {    
                if (json.status == "success")   // the user was added to the database 
                {                            
                    $('tr', tbody).toggleClass('alternate'); 
                    // Toggle the existing rows of the table's alternate class 
                     
                    tbody.prepend(json.html); 
                    // add the new row to the tbody (at the top) 
                     
                    button_bind(); 
                    // bind the new buttons of the user just added 
             
                    count_span.html(parseInt(count_span.html())+1); 
                    // update the user count in the footer 
                     
                    form.dialog('close'); 
                    // close the dialog  
                     
                    showMessage(json.message, 'success'); 
                    // show the success message 
                } 
                else                            // the user was not added 
                {    
                    $('p.feedback', form).removeClass('success, notify').addClass('error').html(json.message).show(); 
                } 
            } 
            }); 
            // send the data via AJAX to our controller 
        } 
    } 
    }); 
     
    form.dialog('open'); 
    // Open the dialog 
}

In the code above, the dialog's buttons are set by passing the buttons object in the dialog
function. Two buttons are set, "Cancel", which merely closes the dialog, and "Add User".

Inside the "Add User" function, the data from the form is collected and stored in an object, data by the following code:

 
var inputs = $('input[type="text"], input[type="hidden"], select', form); 
             
inputs.each(function(){ 
    var el = $(this); 
    data[el.attr('name')] = el.val(); 
});

This data is then sent to our AJAX Controller, at the relative URL ajax/add by the following code:

 
$.ajax({ 
type:       'POST', 
url:        'ajax/add', 
data:       data, 
dataType:   'json',  
success:    function(json)  
{    
    if (json.status == "success")   // the user was added to the database 
    {                            
        $('tr', tbody).toggleClass('alternate'); 
        // Toggle the existing rows of the table's alternate class 
         
        tbody.prepend(json.html); 
        // add the new row to the tbody (at the top) 
         
        button_bind(); 
        // bind the new buttons of the user just added 
 
        count_span.html(parseInt(count_span.html())+1); 
        // update the user count in the footer 
         
        form.dialog('close'); 
        // close the dialog  
         
        showMessage(json.message, 'success'); 
        // show the success message 
    } 
    else                            // the user was not added 
    {    
        $('p.feedback', form).removeClass('success, notify').addClass('error').html(json.message).show(); 
    } 
} 
});

Upon receiving the output from the method in our Controller, if the insert was successful, a new row is added to the table by prepending the table body with the HTML of the new row, outputted from the Controller.

As well as the new row being added, the alternating table rows are switched to keep the design consistent, and the counter in the footer is increased. The dialog window is closed, and a success message is displayed.

Also note, that the button_bind() function is called again after the new row is added. This is very important, as the new buttons that are added to the DOM in the new table row will not already have the code applied to them that the pre-existing buttons do.

If however, the insert failed to take place, either from a database error or failed validation, the message from the controller is displayed in the form's feedback

element.

The final peice of code in the initAdd() function opens the dialog window.

 
form.dialog('open'); 
// Open the dialog

initUpdate()

Much like our Model and Controller methods, the function to update a user record is not too different from the function to add a user.

We shall be using the form dialog again, but setting a different title, buttons, and functions for those buttons to trigger.

Place the following code underneath the initAdd() function:

 
function initUpdate() 
{ 
    var id = $(this).attr('id').split('_')[1]; 
    // get the user id from the button id attribute (user_xxx) 
     
    var row = $('tr#row_'+id, tbody); 
    // the row of the table we are updating 
     
    var first_name      = $('td:nth-child(1)', row).html(); 
    var last_name       = $('td:nth-child(2)', row).html(); 
    var dob             = $('td:nth-child(3)', row).html(); 
    var gender          = $.trim($('td:nth-child(4)', row).html()).substr(0, 1); 
    var email_address   = $('td:nth-child(5)', row).html(); 
    // get the values of the user we are editing from the table HTML 
     
    $('input[name="first_name"]', form).val(first_name); 
    $('input[name="last_name"]', form).val(last_name); 
    $('input[name="dob"]', form).val(dob); 
    $('select[name="gender"]', form).val(gender); 
    $('input[name="email_address"]', form).val(email_address); 
    // fill the form input/select elements with these vales 
 
    form.dialog('option', 'title', 'Update User'); 
    // set the title of the dialog to 'Update User' 
     
    // again, set the buttons and their functions 
    form.dialog({buttons:  
    { 
        'Cancel': function()        // create a 'Cancel' button to close the form 
        { 
            form.dialog('close');    
        }, 
         
        'Update User': function()   // create the 'Update User' button 
        { 
            $('p.feedback', form).removeClass('success, error').addClass('notify'). 
            html('Sending details to server...').show(); 
            // notify the user the details are being sent to the server 
             
            var data = { 
                id: id 
            }; 
            // put the id in the data object  
             
            var inputs = $('input[type="text"], input[type="hidden"], select', form); 
             
            inputs.each(function(){ 
                var el = $(this); 
                data[el.attr('name')] = el.val(); 
            }); 
            // again, collect form data form inputs and select 
             
            $.ajax({ 
            type:       'POST', 
            url:        'ajax/update', 
            data:       data, 
            dataType:   'json',  
            success:    function(json)  
            {    
                if (json.status == "success")   // the user was updated 
                {                            
                    row.after(json.html); 
                    // add new row with updated data underneath 
                     
                    row.next('tr').removeClass().addClass(row.attr('class')); 
                    // give it the same classes as the row we edited 
                     
                    row.remove(); 
                    // remove old row! 
                     
                    button_bind(); 
                    // bind the new buttons 
                     
                    form.dialog('close'); 
                    // close the dialog! 
                     
                    showMessage(json.message, 'success'); 
                    // show the success message 
                } 
                else                            // the user was not updated 
                { 
                    $('p.feedback', form).removeClass('success, notify').addClass('error').html(json.message).show(); 
                } 
            } 
            }); 
            // send the data via AJAX 
        } 
     
    }}); 
     
    form.dialog('open'); 
    // Open the dialog 
}

Let's go through the differences in the two functions.

Straight away, as we are updating a user record, we will need the id of the user we are editing. As the update buttons were given the id attribute of "user_xxx" (where xxx is the id in the table) in the HTML, we shall use this to extract it.

 
var id = $(this).attr('id').split('_')[1];

Now we have the id, we want to create a jQuery object of the table row this user record is being stored in, so we can collect the data, and then also manipulate this row in the DOM. Each table row has been given the id attribute of "row_xxx", so again, this can easily be retrieved.

 
var row = $('tr#row_'+id, tbody); 
// the row of the table we are updating

Next we want to add the current data of the user to the form's input fields, so they are ready to be updated.

 
var first_name      = $('td:nth-child(1)', row).html(); 
var last_name       = $('td:nth-child(2)', row).html(); 
var dob             = $('td:nth-child(3)', row).html(); 
var gender          = $.trim($('td:nth-child(4)', row).html()).substr(0, 1); 
var email_address   = $('td:nth-child(5)', row).html(); 
// get the values of the user we are editing from the table HTML 
 
$('input[name="first_name"]', form).val(first_name); 
$('input[name="last_name"]', form).val(last_name); 
$('input[name="dob"]', form).val(dob); 
$('select[name="gender"]', form).val(gender); 
$('input[name="email_address"]', form).val(email_address); 
// fill the form input/select elements with these vales

The above code assigns the data in each of the 1st to 5th table cells of the row to the variable for which the data corresponds to. The form input and select values are then set to these variables.

The rest of the function is very similar to the initAdd() function, the data is posted again, but this time to ajax/update.

Instead of adding a new table row at the top on a successful update, the row containing the new data is added directly underneath the original row, given the same CSS classes as the original, and then the original row is removed. All these things together give the effect of the row's data simply being updated.

initRemove()

The last of our three functions will of course, delete a user from the database, and remove the row from the table.

Write the following underneath the initUpdate() function:

 
function initRemove() 
{ 
    check = confirm('Are you sure you want to delete this user?'); 
    // confirm if really wants to delete the user 
     
    if (check)  // if they do 
    {    
        var id = $(this).attr('id').split('_')[1]; 
        // get the user id from the button id attribute (user_xxx) 
         
        var row = $('tr#row_'+id, tbody); 
        // the row of the table we are deleting 
         
        $.ajax({ 
        type:       'POST', 
        url:        'ajax/delete', 
        data:       'id='+id, 
        dataType:   'json',  
        success:    function(json)  
        {    
            if (json.status == "success")   // the user was deleted 
            {    
                count_span.html(count_span.html()-1); 
                // decrease the count in the footer 
                 
                row.nextAll('tr').toggleClass('alternate'); 
                // toggle the alternate class for all rows after this row 
                 
                row.fadeOut(); 
                // fade out the row with the record that has been deleted 
                 
                showMessage(json.message, 'success'); 
                // show the success message 
                 
            } 
            else    // the user was not deleted 
            { 
                showMessage(json.message, 'error'); 
                // show the error message 
            } 
        } 
        }); 
    } 
}

The function begins by confirming the user actually wants to delete the user record, by showing a confirm() message

 
check = confirm('Are you sure you want to delete this user?');

If the user does confirm, the id of the user, and the row the data is housed in is again retrieved. The id is then posted to ajax/delete.

If the delete is successful, the user count is decreased, the row faded out and removed, and a success message displayed.

Also, before the row is removed, the rows underneath have their "alternate" classes switched, to keep the zebra design consistent.

If the delete is not successful, an error message is shown.

And that's it. Visit http://yoursite.com/path/to/ci-install/index.php/manage once more, and your user table will now be in full working condition.


Conclusion

If you are fairly new to CodeIgniter or MVC in general, you should hopefully now have some idea of what Models, Controllers and Views are, and how they all work with each other to produce an application on the web.

You should also now be aware of how basic jQuery events work, and be able to use them in projects of your own.

Where to go from here?

You could build on this management tool by adding a requirement that the user be logged in to visit the page, redirecting to a simple login page if not. Be sure to check out the CodeIgniter User Guide if you get stuck at any point.

Useful Links:

  • CodeIgniter User Guide
  • jQuery UI Demos & Documentation

If you have any comments or questions related to this tutorial, CodeIgniter or jQuery, please feel free to leave your thoughts in the comments. Thanks for reading.





Similar content