CodeIgniter CRUD Operations with Search and Pagination

By: CodexWorld | In: CodeIgniter | Last Updated: Jan 22, 2019

CRUD Operations are the most used functionality in CodeIgniter application. Codeigniter CRUD Operations helps to manipulate data (Fetch, Insert, Update, and Delete) in the database. You can view, add, edit, and delete data from the database with CRUD in CodeIgniter. CRUD is very useful to integrate data management functionality in CodeIgniter.

To make the Codeigniter CRUD application user-friendly you can add search and pagination feature. Search and pagination is the must-have functionality for the data management section. When you implementing the CodeIgniter CRUD application, search and pagination are the recommended feature for the data list. In this tutorial, we will show you how to implement CRUD operations in CodeIgniter with search and pagination using MySQL.

In the example code, we will implement the following functionality to integrate CRUD with search filter and pagination in Codeigniter.

Fetch the members data from the database and listed on the web page.
Add pagination links to the data list using CodeIgniter Pagination library.
Search and filter the records.
Add member data to the database.
Edit and update member data in the database.
Delete member data from the database.

Before getting started to create CodeIgniter CRUD application with search and pagination, take a look at the files structure.

codeigniter_crud/
├── application/
│   ├── controllers/
│   │   └── Members.php
│   ├── models/
│   │   └── Member.php
│   └── views/
│       ├── templates/
│       │   ├── header.php
│       │   └── footer.php
│       └── members/
│           ├── index.php
│           ├── view.php
│           └── add-edit.php
└── assets/
    ├── css/
    └── images/

Create Database Table

To store the member’s information a table needs to be created in the database. The following SQL creates a members table with some basic columns in the MySQL database.

CREATE TABLE `members` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
 `country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Config

autoload.php
In the config/autoload.php file, define the library and helper which you like to load automatically on every request.

$autoload['libraries'] = array('database', 'session');
$autoload['helper'] = array('url');

Controller (Members.php)

The Members controller handles the CRUD operations (view, add, edit, and delete).

__construct() – Loads the required library, helper, and model. Also, define the limit of records to be listed in each page.
index() – List members data using getRows() method of Member model.
- Retrieve status messages from SESSION.
- If the search request submitted, retrieve the search term and store in the SESSION.
- Initialize the Pagination library.
- Fetch the records from the database based on the search terms.
- Pass the members data and load the list view.
view() – Display sepcific member data using getRows() method of Member model.
- Fetch the member data from the database based on the specific ID.
- Pass the member data and load the details view.
add() – Add member data to database using insert() method of Member model.
- Initially, the form view is loaded to receive user’s input.
- If the form is submitted,
  - The posted form data is validated using CodeIgniter Form Validation library.
  - Insert member data in the database.
edit() – Edit and update specific member data using update() method of Member model.
- Fetch the member data from the database based on the specific ID.
- The form view is loaded with the pre-filled member data.
- If the form is submitted,
  - The posted form data is validated using CodeIgniter Form Validation library.
  - Update member data in the database.
delete() – Remove member data from the database using delete() method of Member model.

<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Members extends CI_Controller {
    
    function __construct() {
        parent::__construct();
        
        // Load member model
        $this->load->model('member');
        
        // Load form helper and library
        $this->load->helper('form');
        $this->load->library('form_validation');
        
        // Load pagination library
        $this->load->library('pagination');
        
        // Per page limit
        $this->perPage = 5;
    }
    
    public function index(){
        $data = array();
        
        // Get messages from the session
        if($this->session->userdata('success_msg')){
            $data['success_msg'] = $this->session->userdata('success_msg');
            $this->session->unset_userdata('success_msg');
        }
        if($this->session->userdata('error_msg')){
            $data['error_msg'] = $this->session->userdata('error_msg');
            $this->session->unset_userdata('error_msg');
        }
        
        // If search request submitted
        if($this->input->post('submitSearch')){
            $inputKeywords = $this->input->post('searchKeyword');
            $searchKeyword = strip_tags($inputKeywords);
            if(!empty($searchKeyword)){
                $this->session->set_userdata('searchKeyword',$searchKeyword);
            }else{
                $this->session->unset_userdata('searchKeyword');
            }
        }elseif($this->input->post('submitSearchReset')){
            $this->session->unset_userdata('searchKeyword');
        }
        $data['searchKeyword'] = $this->session->userdata('searchKeyword');
        
        // Get rows count
        $conditions['searchKeyword'] = $data['searchKeyword'];
        $conditions['returnType']    = 'count';
        $rowsCount = $this->member->getRows($conditions);
        
        // Pagination config
        $config['base_url']    = base_url().'members/index/';
        $config['uri_segment'] = 3;
        $config['total_rows']  = $rowsCount;
        $config['per_page']    = $this->perPage;
        
        // Initialize pagination library
        $this->pagination->initialize($config);
        
        // Define offset
        $page = $this->uri->segment(3);
        $offset = !$page?0:$page;
        
        // Get rows
        $conditions['returnType'] = '';
        $conditions['start'] = $offset;
        $conditions['limit'] = $this->perPage;
        $data['members'] = $this->member->getRows($conditions);
        $data['title'] = 'Members List';
        
        // Load the list page view
        $this->load->view('templates/header', $data);
        $this->load->view('members/index', $data);
        $this->load->view('templates/footer');
    }

    public function view($id){
        $data = array();
        
        // Check whether member id is not empty
        if(!empty($id)){
            $data['member'] = $this->member->getRows(array('id' => $id));;
            $data['title']  = 'Member Details';
            
            // Load the details page view
            $this->load->view('templates/header', $data);
            $this->load->view('members/view', $data);
            $this->load->view('templates/footer');
        }else{
            redirect('members');
        }
    }
    
    public function add(){
        $data = array();
        $memData = array();
        
        // If add request is submitted
        if($this->input->post('memSubmit')){
            // Form field validation rules
            $this->form_validation->set_rules('first_name', 'first name', 'required');
            $this->form_validation->set_rules('last_name', 'last name', 'required');
            $this->form_validation->set_rules('email', 'email', 'required|valid_email');
            $this->form_validation->set_rules('gender', 'gender', 'required');
            $this->form_validation->set_rules('country', 'country', 'required');
            
            // Prepare member data
            $memData = array(
                'first_name'=> $this->input->post('first_name'),
                'last_name' => $this->input->post('last_name'),
                'email'     => $this->input->post('email'),
                'gender'    => $this->input->post('gender'),
                'country'   => $this->input->post('country')
            );
            
            // Validate submitted form data
            if($this->form_validation->run() == true){
                // Insert member data
                $insert = $this->member->insert($memData);

                if($insert){
                    $this->session->set_userdata('success_msg', 'Member has been added successfully.');
                    redirect('members');
                }else{
                    $data['error_msg'] = 'Some problems occured, please try again.';
                }
            }
        }
        
        $data['member'] = $memData;
        $data['title'] = 'Add Member';
        
        // Load the add page view
        $this->load->view('templates/header', $data);
        $this->load->view('members/add-edit', $data);
        $this->load->view('templates/footer');
    }
    
    public function edit($id){
        $data = array();
        
        // Get member data
        $memData = $this->member->getRows(array('id' => $id));
        
        // If update request is submitted
        if($this->input->post('memSubmit')){
            // Form field validation rules
            $this->form_validation->set_rules('first_name', 'first name', 'required');
            $this->form_validation->set_rules('last_name', 'last name', 'required');
            $this->form_validation->set_rules('email', 'email', 'required|valid_email');
            $this->form_validation->set_rules('gender', 'gender', 'required');
            $this->form_validation->set_rules('country', 'country', 'required');
            
            // Prepare member data
            $memData = array(
                'first_name'=> $this->input->post('first_name'),
                'last_name' => $this->input->post('last_name'),
                'email'     => $this->input->post('email'),
                'gender'    => $this->input->post('gender'),
                'country'   => $this->input->post('country')
            );
            
            // Validate submitted form data
            if($this->form_validation->run() == true){
                // Update member data
                $update = $this->member->update($memData, $id);

                if($update){
                    $this->session->set_userdata('success_msg', 'Member has been updated successfully.');
                    redirect('members');
                }else{
                    $data['error_msg'] = 'Some problems occured, please try again.';
                }
            }
        }

        $data['member'] = $memData;
        $data['title'] = 'Update Member';
        
        // Load the edit page view
        $this->load->view('templates/header', $data);
        $this->load->view('members/add-edit', $data);
        $this->load->view('templates/footer');
    }
    
    public function delete($id){
        // Check whether member id is not empty
        if($id){
            // Delete member
            $delete = $this->member->delete($id);
            
            if($delete){
                $this->session->set_userdata('success_msg', 'Member has been removed successfully.');
            }else{
                $this->session->set_userdata('error_msg', 'Some problems occured, please try again.');
            }
        }
        
        // Redirect to the list page
        redirect('members');
    }
}

Model (Member.php)

The Member model handles the database related works (Fetch, Add, Edit, and Delete).

__construct() – Define the table name.
getRows() – Fetch the members data from the database based on the specified parameters passed in the $params. Returns the filtered records on success.
insert() – Insert member data in the database. Returns the row ID on success, and FALSE on error.
update() – Update member data in the database based on the row ID. Returns TRUE on success, and FALSE on error.
delete() – Delete record from the database based on the row ID. Returns TRUE on success, and FALSE on error.

<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Member extends CI_Model{
    
    function __construct() {
        // Set table name
        $this->table = 'members';
    }
    
    /*
     * Fetch members data from the database
     * @param array filter data based on the passed parameters
     */
    function getRows($params = array()){
        $this->db->select('*');
        $this->db->from($this->table);
        
        if(array_key_exists("conditions", $params)){
            foreach($params['conditions'] as $key => $val){
                $this->db->where($key, $val);
            }
        }
        
        if(!empty($params['searchKeyword'])){
            $search = $params['searchKeyword'];
            $likeArr = array('first_name' => $search, 'last_name' => $search, 'email' => $search);
            $this->db->or_like($likeArr);
        }
        
        if(array_key_exists("returnType",$params) && $params['returnType'] == 'count'){
            $result = $this->db->count_all_results();
        }else{
            if(array_key_exists("id", $params)){
                $this->db->where('id', $params['id']);
                $query = $this->db->get();
                $result = $query->row_array();
            }else{
                $this->db->order_by('first_name', 'asc');
                if(array_key_exists("start",$params) && array_key_exists("limit",$params)){
                    $this->db->limit($params['limit'],$params['start']);
                }elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){
                    $this->db->limit($params['limit']);
                }
                
                $query = $this->db->get();
                $result = ($query->num_rows() > 0)?$query->result_array():FALSE;
            }
        }
        
        // Return fetched data
        return $result;
    }
    
    /*
     * Insert members data into the database
     * @param $data data to be insert based on the passed parameters
     */
    public function insert($data = array()) {
        if(!empty($data)){
            // Add created and modified date if not included
            if(!array_key_exists("created", $data)){
                $data['created'] = date("Y-m-d H:i:s");
            }
            if(!array_key_exists("modified", $data)){
                $data['modified'] = date("Y-m-d H:i:s");
            }
            
            // Insert member data
            $insert = $this->db->insert($this->table, $data);
            
            // Return the status
            return $insert?$this->db->insert_id():false;
        }
        return false;
    }
    
    /*
     * Update member data into the database
     * @param $data array to be update based on the passed parameters
     * @param $id num filter data
     */
    public function update($data, $id) {
        if(!empty($data) && !empty($id)){
            // Add modified date if not included
            if(!array_key_exists("modified", $data)){
                $data['modified'] = date("Y-m-d H:i:s");
            }
            
            // Update member data
            $update = $this->db->update($this->table, $data, array('id' => $id));
            
            // Return the status
            return $update?true:false;
        }
        return false;
    }
    
    /*
     * Delete member data from the database
     * @param num filter data based on the passed parameter
     */
    public function delete($id){
        // Delete member data
        $delete = $this->db->delete($this->table, array('id' => $id));
        
        // Return the status
        return $delete?true:false;
    }
}

Views

templates/
The views/templates/ directory holds the elements (header, footer, etc.) of the web pages.

templates/header.php
In this file, the header part of the web page is placed. The Bootstrap 4 library is used for styling the HTML table and form. So, include the CSS file of the Bootstrap library and include the custom stylesheet file (if any).

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title><?php echo $title; ?> | CodeIgniter CRUD with Search and Pagination</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css">
    <link rel="stylesheet" href="<?php echo base_url('assets/css/style.css'); ?>">
</head>
<body>

templates/footer.php
In this file, the footer part of the web page is placed.

</body>
</html>

members/
The views/members/ directory holds the view files of the Members controller.

members/index.php
Initially, all the members data is retrieved from the database and listed in the webpage with Add, Edit, and Delete link.

The search option is added to the CRUD data list. The user can filter and sort the members list using the search input field.
The pagination links are placed in the bottom of the CRUD data list using create_links() function of Pagination class.
The View link redirects the user to the members/view page to perform a Read operation.
The Add link redirects the user to the members/add page to perform Create operation.
The Edit link redirects the user to the members/edit page to perform the Update operation.
The Delete link redirect user to the members/delete page to perform Delete operation.

<div class="container">
    <h2><?php echo $title; ?></h2>
    
    <!-- Display status message -->
    <?php if(!empty($success_msg)){ ?>
    <div class="col-xs-12">
        <div class="alert alert-success"><?php echo $success_msg; ?></div>
    </div>
    <?php }elseif(!empty($error_msg)){ ?>
    <div class="col-xs-12">
        <div class="alert alert-danger"><?php echo $error_msg; ?></div>
    </div>
    <?php } ?>
    
    <div class="row">
        <div class="col-md-12 search-panel">
            <!-- Search form -->
            <form method="post">
                <div class="input-group mb-3">
                    <input type="text" name="searchKeyword" class="form-control" placeholder="Search by keyword..." value="<?php echo $searchKeyword; ?>">
                    <div class="input-group-append">
                        <input type="submit" name="submitSearch" class="btn btn-outline-secondary" value="Search">
                        <input type="submit" name="submitSearchReset" class="btn btn-outline-secondary" value="Reset">
                    </div>
                </div>
            </form>
            
            <!-- Add link -->
            <div class="float-right">
                <a href="<?php echo site_url('members/add/'); ?>" class="btn btn-success"><i class="plus"></i> New Member</a>
            </div>
        </div>
        
        <!-- Data list table --> 
        <table class="table table-striped table-bordered">
            <thead class="thead-dark">
                <tr>
                    <th>#</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Email</th>
                    <th>Gender</th>
                    <th>Country</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody>
                <?php if(!empty($members)){ foreach($members as $row){ ?>
                <tr>
                    <td><?php echo $row['id']; ?></td>
                    <td><?php echo $row['first_name']; ?></td>
                    <td><?php echo $row['last_name']; ?></td>
                    <td><?php echo $row['email']; ?></td>
                    <td><?php echo $row['gender']; ?></td>
                    <td><?php echo $row['country']; ?></td>
                    <td>
                        <a href="<?php echo site_url('members/view/'.$row['id']); ?>" class="btn btn-primary">view</a>
                        <a href="<?php echo site_url('members/edit/'.$row['id']); ?>" class="btn btn-warning">edit</a>
                        <a href="<?php echo site_url('members/delete/'.$row['id']); ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete?')">delete</a>
                    </td>
                </tr>
                <?php } }else{ ?>
                <tr><td colspan="7">No member(s) found...</td></tr>
                <?php } ?>
            </tbody>
        </table>
    
        <!-- Display pagination links -->
        <div class="pagination pull-right">
            <?php echo $this->pagination->create_links(); ?>
        </div>
    </div>
</div>

members/view.php
This view is loaded by the view() function of members controller. The specific member details are displayed in the Bootstrap card view.

<div class="container">
    <h2><?php echo $title; ?></h2>
    <div class="col-md-6">
        <div class="card" style="width:400px">
            <div class="card-body">
                <h4 class="card-title"><?php echo $member['first_name'].' '.$member['last_name']; ?></h4>
                <p class="card-text"><b>Email:</b> <?php echo $member['email']; ?></p>
                <p class="card-text"><b>Gender:</b> <?php echo $member['gender']; ?></p>
                <p class="card-text"><b>Country:</b> <?php echo $member['country']; ?></p>
                <p class="card-text"><b>Created:</b> <?php echo $member['created']; ?></p>
                <a href="<?php echo site_url('members'); ?>" class="btn btn-primary">Back To List</a>
            </div>
        </div>
    </div>
</div>

members/add-edit.php
This view is loaded by the add() & edit() functions of members controller.

On add request, an HTML form is displayed to receive the user’s input (name, edit, gender, country, etc.).
On edit request, an HTML form is displayed with pre-filled member data in the input fields.

<div class="container">
    <h2><?php echo $title; ?></h2>
    
    <!-- Display status message -->
    <?php if(!empty($success_msg)){ ?>
    <div class="col-xs-12">
        <div class="alert alert-success"><?php echo $success_msg; ?></div>
    </div>
    <?php }elseif(!empty($error_msg)){ ?>
    <div class="col-xs-12">
        <div class="alert alert-danger"><?php echo $error_msg; ?></div>
    </div>
    <?php } ?>
    
    <div class="row">
        <div class="col-md-6">
            <form method="post">
                <div class="form-row">
                    <div class="col-md-6 mb-3">
                        <label>First name</label>
                        <input type="text" class="form-control" name="first_name" placeholder="Enter first name" value="<?php echo !empty($member['first_name'])?$member['first_name']:''; ?>" >
                        <?php echo form_error('first_name','<div class="invalid-feedback">','</div>'); ?>
                    </div>
                    <div class="col-md-6 mb-3">
                        <label>Last name</label>
                        <input type="text" class="form-control" name="last_name" placeholder="Enter last name" value="<?php echo !empty($member['last_name'])?$member['last_name']:''; ?>" >
                        <?php echo form_error('last_name','<div class="invalid-feedback">','</div>'); ?>
                    </div>
                </div>
                <div class="form-group">
                    <label>Email</label>
                    <input type="text" class="form-control" name="email" placeholder="Enter email" value="<?php echo !empty($member['email'])?$member['email']:''; ?>" >
                    <?php echo form_error('email','<div class="invalid-feedback">','</div>'); ?>
                </div>
                <div class="form-group">
                    <label>Gender</label>
                    <div class="custom-control custom-radio custom-control-inline">
                        <input type="radio" id="gender1" name="gender" class="custom-control-input" value="Male" <?php echo empty($member['gender']) || (!empty($member['gender']) && ($member['gender'] == 'Male'))?'checked="checked"':''; ?> >
                        <label class="custom-control-label" for="gender1">Male</label>
                    </div>
                    <div class="custom-control custom-radio custom-control-inline">
                        <input type="radio" id="gender2" name="gender" class="custom-control-input" value="Female" <?php echo (!empty($member['gender']) && ($member['gender'] == 'Female'))?'checked="checked"':''; ?> >
                        <label class="custom-control-label" for="gender2">Female</label>
                    </div>
                    <?php echo form_error('gender','<div class="invalid-feedback">','</div>'); ?>
                </div>
                <div class="form-group">
                    <label>Country</label>
                    <input type="text" class="form-control" name="country" placeholder="Enter country" value="<?php echo !empty($member['country'])?$member['country']:''; ?>" >
                    <?php echo form_error('country','<div class="invalid-feedback">','</div>'); ?>
                </div>
                
                <a href="<?php echo site_url('members'); ?>" class="btn btn-secondary">Back</a>
                <input type="submit" name="memSubmit" class="btn btn-success" value="Submit">
            </form>
        </div>
    </div>
</div>

Remove the index.php from URL

By default, the index.php file will be included in the URLs of CRUD operation requests. Follow the below steps to remove index.php from the URLs in CodeIgniter.

In the config/config.php file, remove index.php from index_page variable and set it blank.
```
$config['index_page'] = '';
```

Create a HTACCESS (.htaccess) file in CodeIgniter’s root directory and write the following URL Rewrite Rule code.

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L]

Note that: The mod_rewrite extension must be enabled in your server. You need to specify the path as per the location of your application (RewriteRule ^(.*)$ /folder_name/index.php/$1 [L])

Conclusion

Our sample CRUD application with CodeIgniter and MySQL helps you to implement data management (fetch, insert, update, and delete) functionality in the CodeIgniter framework. This example script adds pagination and search features to make the CodeIgniter CRUD data list user-friendly. We used Bootstrap library for styling the form and table, so, the minimal CSS is required to build the list and form view. Also, you can easily enhance the CodeIgniter CRUD with search and pagination script functionality as per your needs.

Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request

Download Source Code

Add WYSIWYG HTML Editor to Textarea with CKEditor

2Checkout Payment Gateway Integration in PHP