HTML table Listing, Searching and Sorting Using Codeigniter and Mysql – II

We have created html table listing using codeigniter and mysql database in previous codeigniter tutorial.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.

How to add Pagination in HTML table Listing

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.

$this->load->helper('url');
$this->load->library("pagination");

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

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'] = '« First';
            $config['first_tag_open'] = '<li class="prev page">';
            $config['first_tag_close'] = '</li>';
 
            $config['last_link'] = 'Last »';
            $config['last_tag_open'] = '<li class="next page">';
            $config['last_tag_close'] = '</li>';
 
            $config['next_link'] = 'Next →';
            $config['next_tag_open'] = '<li class="next page">';
            $config['next_tag_close'] = '</li>';
 
            $config['prev_link'] = '← 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,

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.

<div><?php echo $links; ?></div>

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.

$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.

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.

 <?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.

<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.

$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.

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.