HTML table Listing, Searching and Sorting Using Codeigniter and Mysql

In this Codeigniter tutorial, I will discuss with you, how to fetch records from MySQL database and bind with HTML table with searching, sorting and pagination.We will add some configuration parameters with Codeigniter using config and auto-loader file.

These are very common features of any html table grid listing.You need to provide pagination,sorting and searching on listed records.I will go through following points into this Codeigniter 3 tutorial,

  • We will create database connection with mysql
  • Retrieve data from the database table and display it into a html table
  • Added pagination on HTML table Listing
  • Added sorting on HTML table Listing
  • Added searching on HTML table Listing

Codeigniter is MVC (Model–View–Controller) based PHP framework, So next in your mind What is MVC? MVC is design pattern that help to create separate layer of application.View will for render HTML,The Model is responsible for handling data related operation,Controller will use for handling communication between View and Model.

You can get basic CI configuration information from jQuery datatable Listing CI and template integration using Simple Layout Example in Codeigniter.

Codeigniter 3 Example of Listing, Pagination, Sorting and Searching

We will create controller, model and view file for listing and other features functionality. We will follow following steps to create pagination,sorting and searching on table listing using codeigniter.

Create Database and table

Now create test name database into mysql server and will be creating a table named employee into the 'test' database.

Select Code
1
2
3
4
5
6
7
8
9
10
11
--
-- Table structure for table `employee`
--
 
CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL COMMENT 'primary key',
  `employee_name` varchar(255) NOT NULL COMMENT 'employee name',
  `employee_salary` double NOT NULL COMMENT 'employee salary',
  `employee_age` int(11) NOT NULL COMMENT 'employee age',
  `profile_image` varchar(255) NOT NULL DEFAULT 'images/default_profile.png'
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=latin1 COMMENT='datatable demo table';

Create Database Connection

Now we have database and table so next step would be create database connection with Codeigniter. We will define database host, database name, database username and database password in a CI config file in codeigniter. The file is located at application/config/database.php.

Select Code
1
2
3
4
'hostname' => 'localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'test',

HTML table listing in CI

We will create table listing using mysql table.We will create controller file that will handle action and render view.We will create model method to get results from mysql database.

Configured model with controller in Codeigniter

We will call model in constructor method in controller file home.php.This method will handle other dependency library as well,

Select Code
1
2
3
4
5
6
public function __construct()
    {
        header("Access-Control-Allow-Origin: *");
        parent::__construct();
        $this->load->model('employee_m', 'employee');
    }

I have added cross origin header in __construct method and added employee model class.

Step 1: Created home.php file in view folder and added HTML table listing View file in this file.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<div class="">
<h1 style="font-size:20pt">CI Example Record listing With Searching,Sorting and Pagination</h1>
<table class="table table-bordered table-hover" cellspacing="0" width="100%" id="employee">
<thead>
<tr>
<th>Name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</thead>
<tbody>
<?php $i=1;foreach($data as $rec): ?>
        <tr>
            <td><?php echo $rec->employee_name; ?></td>
            <td><?php echo $rec->employee_salary; ?></td>
<td><?php echo $rec->employee_age; ?></td>
 
        </tr>
<?php endforeach; ?>
</tbody>
</table>
</div>

We will make default controller home using routes.php file.

$route['default_controller'] = 'home';

Step 2: Created Index controller method to handle listing request.

Select Code
1
2
3
4
5
6
7
8
9
10
public function index()
{
            $data = array();
            $data['title'] = 'Home';
            $config = array();
            $config["base_url"] = base_url().'home/index';
            $data["data"] = $this->employee->get_employees();
       
            $this->template->load('default_layout', 'contents' , 'home', $data);
}

Step 3: Create model file ’employee_m.php’ and added model method to fetch records from database.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
class employee_m extends CI_Model {
 
    var $table = 'employee';
    var $column_order = array(null, 'employee_name','employee_salary','employee_age'); //set column field database for datatable orderable
    var $column_search = array('employee_name','employee_salary','employee_age'); //set column field database for datatable searchable
    var $order = array('id' => 'asc'); // default order
 
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }
    function get_employees($id=0)
    {
                        if(empty($id)){
                                    $query = $this->db->get('employee');
                                                                        if ($query->num_rows() > 0) {
                                    foreach ($query->result() as $row) {
                                    $data[] = $row;
                                    }
                                    return $data;
                                    }
                                    return false;
                        } else {
                                    $query = $this->db->get_where('employee', array('id' => $id));
                                    return $query->row_array();
                        }
            }
}

I have used 'id' as parameters that help to get single employee record from database, otherwise we need to create two method one for all employee record and other for single record.

How to add Pagination in HTML table Listing

We have created html table listing using codeigniter and mysql database.We will add advanced feature in this table like any other table grid plugin.We will follow following steps to add pagination features.

I am using codeigniter pagination library for paging and url library for anchor tag.CI is providing many useful library classes that help to add beautiful features with in finger tips.

one of them is pagination class that use to set pagination parameters like no of records to show per page, next, prev icons, uri segment etc.We need to add pagination and url library into controller constructor method.

Select Code
1
2
$this->load->helper('url');
$this->load->library("pagination");

Modify index() controller method that will configure pagination parameters like below,

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
public function index()
{
            $data = array();
            $data['title'] = 'Home';
            $config = array();
            $config["base_url"] = base_url().'home/index';
            $config["total_rows"] = $this->employee->record_count();
            $config["per_page"] = 10;
            $config["uri_segment"] = 3;
            $config['full_tag_open'] = '<ul class="pagination">';
            $config['full_tag_close'] = '</ul>';
            $config['first_link'] = '&laquo; First';
            $config['first_tag_open'] = '<li class="prev page">';
            $config['first_tag_close'] = '</li>';
 
            $config['last_link'] = 'Last &raquo;';
            $config['last_tag_open'] = '<li class="next page">';
            $config['last_tag_close'] = '</li>';
 
            $config['next_link'] = 'Next &rarr;';
            $config['next_tag_open'] = '<li class="next page">';
            $config['next_tag_close'] = '</li>';
 
            $config['prev_link'] = '&larr; Previous';
            $config['prev_tag_open'] = '<li class="prev page">';
            $config['prev_tag_close'] = '</li>';
 
            $config['cur_tag_open'] = '<li class="active"><a href="">';
            $config['cur_tag_close'] = '</a></li>';
 
            $config['num_tag_open'] = '<li class="page">';
            $config['num_tag_close'] = '</li>';
            $page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;
            $data["data"] = $this->employee->get_employees($config["per_page"], $page);
            $this->pagination->initialize($config);
            $data["links"] = $this->pagination->create_links();
                                   
            $this->template->load('default_layout', 'contents' , 'home', $data);
}

Modify getemployee() model method and passed limit parameters for pagination,

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
function get_employees($limit, $start, $id=0)
{
            if(empty($id)){
                        $this->db->limit($limit, $start);
                        $query = $this->db->get('employee');
                        if ($query->num_rows() > 0) {
                                    foreach ($query->result() as $row) {
                                                $data[] = $row;
                                    }
                        return $data;
            }
            return false;
            } else {
            $query = $this->db->get_where('employee', array('id' => $id));
            return $query->row_array();
            }
}
public function record_count() {
   return $this->db->count_all("employee");
}

Now we will add pagination link on the bottom of table listing into home.php view file.

How to add sorting in HTML table listing Using codeigniter and MySQL

We have already added table listing with pagination, now we will add sorting on table column.I am creating single column sorting on table listing server side.

Step 1: We will change index() action method in home.php controller file.We will add two more parameters in uri for field_name and orderBy parameters.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$data['sort_cols'] = array(
    'employee_name' => 'Name',
    'employee_salary' => 'Salary',
    'employee_age' => 'Age'
);
    
$config["uri_segment"] = 5;

$data['sort_by'] = $this->uri->segment(3, 'employee_name');
$orderBy = $this->uri->segment(4, "desc");

if($orderBy == "asc") $data['sort_order'] = "desc"; else $data['sort_order'] = "asc";

$config["base_url"] = base_url().'home/index/'.$data['sort_by'].'/'.$orderBy.'/';
$data["data"] = $this->employee->get_employees($config["per_page"], $offset, $data['sort_by'], $data['sort_order']);

Step 2: We need to modify employee_m.php model file and passed $sortfield and $orderBy parameters for sorting records.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
function get_employees($per_page, $offset, $sortfield, $orderBy, $id=0)
{
    if(empty($id)){
        //echo $per_page.'fff'.$offset.'fff'.$sortfield.'fff'.$orderBy;
        $this->db->order_by("$sortfield", "$orderBy");
        $this->db->limit($per_page,$offset);
        $query = $this->db->get('employee');
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                        $data[] = $row;
            }
        return $data;
    }
    return false;
    } else {
        $query = $this->db->get_where('employee', array('id' => $id));
        return $query->row_array();
    }
}

Step 3: We will add anchor tab on table header column.I am using anchor() helper method to create anchor tag with in th tag.

Select Code
1
2
3
<?php foreach($sort_cols as $field_name => $field_display): ?>
    <th><?php echo anchor('home/index/'.$field_name.'/'.($sort_by == $field_name ? $sort_order : 'asc').'/'.$page, $field_display); ?></th>
<?php endforeach;?>

We will refreshed table listing and found that th has been converted into anchor link, onclick of anchor th we will sort order of particular column field.

CodeIgniter listing with Searching

We will implement last phase of this tutorial.We will add searching functionality of table listing. Created a search input box that will take search string as a input and added a submit button for submit search string to controller method.I am using uri parameters for search string however you can pass using query string with pagination.

Step 1: We will create search input form which will have a submit button and input element in home.php view file.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<form class="form-search" method="post" action="<?php echo $url;?>">
            <div class="input-group">
                <span class="input-group-addon">
                    <i class="ace-icon fa fa-check"></i>
                </span>

                <input type="text" class="form-control search-query" placeholder="Type your search word" name="search" id="search" value="<?php echo $search_string;?>">
                <span class="input-group-btn">
                    <button type="submit" class="btn btn-purple btn-sm">
                        <span class="ace-icon fa fa-search icon-on-right bigger-110"></span>
                        Search
                    </button>
                </span>
            </div>
        </form>

Step 2: We will change index() action method in home.php controller file.We will add search term parameters in uri for search record in mysql table.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$search_string = $this->input->post('search');
$data['search_string'] = '';
if(!empty($search_string)) {
    
    $this->uri->segment(6, $this->uri->segment(5, 1));
    $data['search_string'] = $this->uri->segment(5, $search_string);
    
} elseif($this->uri->segment(5) != null && !empty($this->uri->segment(5)) && $this->uri->segment(6) != null) {
    $data['search_string'] = $this->uri->segment(5);
}
//set default page uri 
$page_uri = 5;

if(!empty($data['search_string']))
$page_uri = 6;

$config["uri_segment"] = $page_uri;

$config["total_rows"] = $this->employee->record_count($data['search_string']);

$data['page'] = $this->uri->segment($page_uri, 1);
$config["base_url"] = base_url().'home/index/'.$data['sort_by'].'/'.$orderBy.'/'.$data['search_string'];
$data["data"] = $this->employee->get_employees($config["per_page"], $offset, $data['sort_by'], $data['sort_order'], $data['search_string']);

Step 3: We will modify employee_m.php model file get_employees() method and passed search term parameters for filter records.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
function get_employees($per_page, $offset, $sortfield, $orderBy, $search_string, $id=0)
    {
    if(empty($id)){
        //echo $per_page.'fff'.$offset.'fff'.$sortfield.'fff'.$orderBy;
        if(!empty($search_string)) {
            $this->db->like('employee_name',$search_string);
            $this->db->or_like('employee_age',$search_string);
            $this->db->or_like('employee_salary',$search_string);
        }
        $this->db->order_by("$sortfield", "$orderBy");
        $this->db->limit($per_page,$offset);
        $query = $this->db->get('employee');
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $data[] = $row;
            }
        return $data;
    }
    return false;
    } else {
    $query = $this->db->get_where('employee', array('id' => $id));
    return $query->row_array();
    }
    }
    public function record_count($search_string) {
    if(!empty($search_string)) {
        $this->db->like('employee_name',$search_string);
        $this->db->or_like('employee_age',$search_string);
        $this->db->or_like('employee_salary',$search_string);
    }
    return $this->db->count_all_results("employee");
    }

conclusion:

This codeigniter tutorial help to create connection with MySQL database and fetch records to display into HTML table.We have added pagination using codeigniter pagination library an sorting records.I have also demonstrate to add search records into CI html table listing using URI.

You can download source code from below link.