How To Use DataTables In Codeigniter

Maangatech > Web Design > How To Use DataTables In Codeigniter

ci and datatable

CRUD task it can be a challenging and time-consuming task if you will not use various available frameworks and libraries to do it. DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, and it will add advanced interaction controls to any HTML table. In this tutorial, I will show you how to use DataTable in Codeigniter.

Procedures on how to use DataTables in Codeigniter

1. Download and Install Codeigniter

Make sure you have downloaded and installed Codeigniter framework on your server. If you have any problem relating to Codeigniter installation you can refer Codeigniter installation manual.

2. Create MySQL Database for storing our table data

First, create a database named ‘myapp’ if you have not yet created the database. Then run the below SQL command to create and import data to ‘Job_positions’ table. Make sure your database is connected with you Codeigniter application.

-- 
-- Table structure for table `job_positions` 
-- 
 
CREATE TABLE IF NOT EXISTS `job_positions` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `job_position` varchar(200) DEFAULT NULL, 
  `description` varchar(200) DEFAULT NULL, 
  `added_date` date NOT NULL, 
  `updated_on` datetime NOT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; 
 
-- 
-- Dumping data for table `job_positions` 
-- 
 
INSERT INTO `job_positions` (`id`, `job_position`, `description`, `added_date`, `updated_on`) VALUES 
(1, 'Customer care', 'Dealing with customer care', '2016-06-01', '0000-00-00 00:00:00'), 
(2, 'IT Staff', 'Dealing with ICT', '2015-12-02', '2015-12-02 00:00:00'), 
(3, 'Bursar', 'Dealing with financial activities', '2015-12-02', '2015-12-11 00:00:00'), 
(4, 'Sales Officer', 'Dealing with Sales tasks', '2016-06-01', '0000-00-00 00:00:00'); 
 
-- --------------------------------------------------------

3. Create the Model (‘Job_positions_model’)

Create a model file named ‘Job_positions_model’ inside ‘application/models’ folder. Then copy paste the below code to the file.

<?php 
class Job_positions_model extends CI_Model 
 
    { 
    public $table = 'job_positions'; 
 
    public $id = 'id'; 
 
    public $order = 'DESC'; 
 
    function __construct() 
        { 
        parent::__construct(); 
        } 
 
    // get all 
 
    function get_all() 
        { 
        $this->db->order_by($this->id, $this->order); 
        return $this->db->get($this->table)->result(); 
        } 
 
    // get data by id 
 
    function get_by_id($id) 
        { 
        $this->db->where($this->id, $id); 
        return $this->db->get($this->table)->row(); 
        } 
 
    // insert data 
 
    function insert($data) 
        { 
        $this->db->insert($this->table, $data); 
        } 
 
    // update data 
 
    function update($id, $data) 
        { 
        $this->db->where($this->id, $id); 
        $this->db->update($this->table, $data); 
        } 
 
    // delete data 
 
    function delete($id) 
        { 
        $this->db->where($this->id, $id); 
        $this->db->delete($this->table); 
        } 
    }

This model contains CRUD functions including adding, reading and updating functions.

4. Create the Controller (‘Job_positions’)

Create a controller file named ‘Job_positions’ inside ‘application/controllers’ folder. Then copy paste the below code to the file.

<?php 
 
if (!defined('BASEPATH')) exit('No direct script access allowed'); 
class Job_positions extends CI_Controller 
 
    { 
    function __construct() 
        { 
        parent::__construct(); 
        $this->load->model('Job_positions_model'); 
        $this->load->library('form_validation'); 
        } 
 
    public 
 
    function index() 
        { 
        $job_positions = $this->Job_positions_model->get_all(); 
        $data = array( 
            'job_positions_data' => $job_positions 
        ); 
        $this->load->view('job_positions_list', $data); 
        } 
 
    public 
 
    function read($id) 
        { 
        $row = $this->Job_positions_model->get_by_id($id); 
        if ($row) 
            { 
            $data = array( 
                'id' => $row->id, 
                'job_position' => $row->job_position, 
                'description' => $row->description, 
                'added_date' => $row->added_date, 
                'updated_on' => $row->updated_on, 
            ); 
            $this->load->view('job_positions_read', $data); 
            } 
          else 
            { 
            $this->session->set_flashdata('message', 'Record Not Found'); 
            redirect(site_url('job_positions')); 
            } 
        } 
 
    public 
 
    function create() 
        { 
        $data = array( 
            'button' => 'Create', 
            'action' => site_url('job_positions/create_action') , 
            'id' => set_value('id') , 
            'job_position' => set_value('job_position') , 
            'description' => set_value('description') , 
        ); 
        $this->load->view('job_positions_form', $data); 
        } 
 
    public 
 
    function create_action() 
        { 
        $this->_rules(); 
        if ($this->form_validation->run() == FALSE) 
            { 
            $this->create(); 
            } 
          else 
            { 
            $data = array( 
                'job_position' => $this->input->post('job_position', TRUE) , 
                'description' => $this->input->post('description', TRUE) , 
                'added_date' => date('Y-m-d H:i:s') , 
            ); 
            $this->Job_positions_model->insert($data); 
            $this->session->set_flashdata('message', 'Create Record Success'); 
            redirect(site_url('job_positions')); 
            } 
        } 
 
    public 
 
    function update($id) 
        { 
        $row = $this->Job_positions_model->get_by_id($id); 
        if ($row) 
            { 
            $data = array( 
                'button' => 'Update', 
                'action' => site_url('job_positions/update_action') , 
                'id' => set_value('id', $row->id) , 
                'job_position' => set_value('job_position', $row->job_position) , 
                'description' => set_value('description', $row->description) , 
            ); 
            $this->load->view('job_positions_form', $data); 
            } 
          else 
            { 
            $this->session->set_flashdata('message', 'Record Not Found'); 
            redirect(site_url('job_positions')); 
            } 
        } 
 
    public 
 
    function update_action() 
        { 
        $this->_rules(); 
        if ($this->form_validation->run() == FALSE) 
            { 
            $this->update($this->input->post('id', TRUE)); 
            } 
          else 
            { 
            $data = array( 
                'job_position' => $this->input->post('job_position', TRUE) , 
                'description' => $this->input->post('description', TRUE) , 
                'updated_on' => date('Y-m-d H:i:s') , 
            ); 
            $this->Job_positions_model->update($this->input->post('id', TRUE) , $data); 
            $this->session->set_flashdata('message', 'Update Record Success'); 
            redirect(site_url('job_positions')); 
            } 
        } 
 
    public 
 
    function delete($id) 
        { 
        $row = $this->Job_positions_model->get_by_id($id); 
        if ($row) 
            { 
            $this->Job_positions_model->delete($id); 
            $this->session->set_flashdata('message', 'Delete Record Success'); 
            redirect(site_url('job_positions')); 
            } 
          else 
            { 
            $this->session->set_flashdata('message', 'Record Not Found'); 
            redirect(site_url('job_positions')); 
            } 
        } 
 
    public 
 
    function _rules() 
        { 
        $this->form_validation->set_rules('job_position', 'job position', 'trim|required'); 
        $this->form_validation->set_rules('description', 'description', 'trim|required'); 
        $this->form_validation->set_rules('id', 'id', 'trim'); 
        $this->form_validation->set_error_delimiters('<span class="text-danger">', '</span>'); 
        } 
    }

This controller will fetch data from the model and finally pass them to view and vice versa.

5. Create the View files

We have created model and controller, now it’s time to build the user interface. Here I will add DataTables codes from CDN at the top of our list view file. You can select various DataTable features on DataTable Download page. I have also added Bootstrap CSS+JS and Jquery on all views. Now create three view files named “job_positions_list” “job_positions_read” “job_positions_form” inside ‘application/views’ folder and copy and paste the below code to them.
Snippet for job_positions_list this file will list all job positions.

<!doctype html> 
<html> 
<head> 
    <title>DataTables and Codeigniter</title> 
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> 
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script> 
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> 
    <!--data table--> 
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs/pdfmake-0.1.18/dt-1.10.12/b-1.2.2/b-colvis-1.2.2/b-html5-1.2.2/b-print-1.2.2/r-2.1.0/datatables.min.css" /> 
    <script type="text/javascript" src="https://cdn.datatables.net/v/bs/pdfmake-0.1.18/dt-1.10.12/b-1.2.2/b-colvis-1.2.2/b-html5-1.2.2/b-print-1.2.2/r-2.1.0/datatables.min.js"></script> 
    <!--/.data table--> 
    <style> 
        body { 
            padding: 15px; 
        } 
    </style> 
</head> 
<body> 
    <div class="row" style="margin-bottom: 10px"> 
        <div class="col-md-4"> 
            <h2 style="margin-top:0px">Job_positions List</h2> 
        </div> 
        <div class="col-md-4 text-center"> 
            <div style="margin-top: 4px" id="message"> 
                <?php echo $this->session->userdata('message') <> '' ? $this->session->userdata('message') : ''; ?> 
            </div> 
        </div> 
        <div class="col-md-4 text-right"> 
            <?php echo anchor(site_url('job_positions/create'), 'Create', 'class="btn btn-primary"'); ?> 
        </div> 
    </div> 
    <table class="table table-bordered table-striped" id="mytable"> 
        <thead> 
            <tr> 
                <th width="80px">No</th> 
                <th>Job Position</th> 
                <th>Description</th> 
                <th>Added Date</th> 
                <th>Updated On</th> 
                <th>Action</th> 
            </tr> 
        </thead> 
        <tbody> 
            <?php 
            $start = 0; 
            foreach ($job_positions_data as $job_positions) 
            { 
                ?> 
                <tr> 
                    <td> 
                        <?php echo ++$start ?> 
                    </td> 
                    <td> 
                        <?php echo $job_positions->job_position ?> 
                    </td> 
                    <td> 
                        <?php echo $job_positions->description ?> 
                    </td> 
                    <td> 
                        <?php echo $job_positions->added_date ?> 
                    </td> 
                    <td> 
                        <?php echo $job_positions->updated_on ?> 
                    </td> 
                    <td style="text-align:center" width="200px"> 
                        <?php  
            echo anchor(site_url('job_positions/read/'.$job_positions->id),'Read');  
            echo ' | ';  
            echo anchor(site_url('job_positions/update/'.$job_positions->id),'Update');  
            echo ' | ';  
            echo anchor(site_url('job_positions/delete/'.$job_positions->id),'Delete','onclick="javasciprt: return confirm(\'Are You Sure ?\')"');  
            ?> 
                    </td> 
                </tr> 
                <?php 
            } 
            ?> 
        </tbody> 
    </table> 
    <script type="text/javascript"> 
        $(document).ready(function() { 
            $("#mytable").dataTable(); 
        }); 
    </script> 
</body> 
</html>

Snippet for job_positions_read this file will be used to view single job position.

<!doctype html> 
<html> 
<head> 
    <title>DataTable and Codeigniter - Read</title> 
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> 
    <style> 
        body { 
            padding: 15px; 
        } 
    </style> 
</head> 
 
<body> 
    <h2 style="margin-top:0px">Job_positions Read</h2> 
    <table class="table"> 
        <tr> 
            <td>Job Position</td> 
            <td> 
                <?php echo $job_position; ?> 
            </td> 
        </tr> 
        <tr> 
            <td>Description</td> 
            <td> 
                <?php echo $description; ?> 
            </td> 
        </tr> 
        <tr> 
            <td>Added Date</td> 
            <td> 
                <?php echo $added_date; ?> 
            </td> 
        </tr> 
        <tr> 
            <td>Updated On</td> 
            <td> 
                <?php echo $updated_on; ?> 
            </td> 
        </tr> 
        <tr> 
            <td></td> 
            <td><a href="<?php echo site_url('job_positions') ?>" class="btn btn-default">Cancel</a></td> 
        </tr> 
    </table> 
</body> 
</html>

Snippet for job_positions_form this will contain add and update form.

<!doctype html> 
<html> 
<head> 
    <title>DataTable and Codeigniter - Form</title> 
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> 
    <style> 
        body { 
            padding: 15px; 
        } 
    </style> 
</head> 
<body> 
    <h2 style="margin-top:0px">Job_positions <?php echo $button ?></h2> 
    <form action="<?php echo $action; ?>" method="post"> 
        <div class="form-group"> 
            <label for="varchar">Job Position 
                <?php echo form_error('job_position') ?> 
            </label> 
            <input type="text" class="form-control" name="job_position" id="job_position" placeholder="Job Position" value="<?php echo $job_position; ?>" /> 
        </div> 
        <div class="form-group"> 
            <label for="varchar">Description 
                <?php echo form_error('description') ?> 
            </label> 
            <input type="text" class="form-control" name="description" id="description" placeholder="Description" value="<?php echo $description; ?>" /> 
        </div> 
        <input type="hidden" name="id" value="<?php echo $id; ?>" /> 
        <button type="submit" class="btn btn-primary"> 
            <?php echo $button ?> 
        </button> 
        <a href="<?php echo site_url('job_positions') ?>" class="btn btn-default">Cancel</a> 
    </form> 
</body> 
</html>

NB. Make sure you have loaded URL, Session and database in autoload.

6. Your work is ready for testing. Visit http://yoururl.com/index.php/Job_positions

SEE DEMO

If you followed the procedures correctly you are now having DataTable integrated with Codeigniter in your application. Please feel free to share this post or to leave a comment.

Related Post