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

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.

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

    '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,

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.

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

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.

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.

You can read CI searching and sorting features in next HTML table Listing – II tutorial.