loading

How to CodeIgniter CRUD Operations with Search and Pagination

How to CodeIgniter CRUD Operations with Search and Pagination

How to CodeIgniter CRUD Operations with Search and Pagination

0 Sales

Free

The most often utilised capability in a CodeIgniter application is CRUD Operations. Codeigniter CRUD Operations allow you to manipulate data in the database (Fetch, Insert, Update, and Delete). CRUD in CodeIgniter allows you to browse, add, modify, and remove data from a database. CRUD is a great way to include data management capabilities into CodeIgniter.

You may add search and pagination features to the Codeigniter CRUD application to make it more user-friendly. The data management part must offer search and pagination capability. Search and pagination are suggested features for the data list while creating the CodeIgniter CRUD application. In this article, we'll teach you how to use MySQL to build CRUD functions in CodeIgniter, including search and pagination.

In the sample code, we will develop the following functionality in Codeigniter to connect CRUD with a search filter and pagination.

 - Get the members' information from the database and display it on the web page.
 - Using the CodeIgniter Pagination library, add pagination links to the data list.
 - Filter and search the records.
 - Fill up the blanks in the database with member information.
 - Edit and keep updating member data in the database.
 - Delete the database's member data.

Examine the file structure before beginning to build a CodeIgniter CRUD application with search and pagination.

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

A table in the database must be built to contain the member's information. In the MySQL database, the following SQL creates a members table with some basic columns.

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

Define the library and helper that you want to load automatically on every request in the config/autoload.php file.

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

Controller (Members.php)

The CRUD actions are handled by the Members controller (view, add, edit, and delete).

1. __construct() – Loads the necessary libraries, helpers, and models. Define the maximum number of records to be displayed on each page.
2. index() – List members data using getRows() function of Member model.
      - SESSION status messages should be retrieved.
      - If the search request was successful, obtain the search phrase and save it in the SESSION.
      - Start the Pagination library.
      - Retrieve the records from the database using the search keywords.

      - Reload the list view after passing the members data.

3. view() – Use the getRows() method of the Member model to display specific member data.

      - Using the particular ID, retrieve the member data from the database.
      - Load the details view after passing the member data.

4. add() – Insert member data into the database using the insert() function of the Member model.

      - The form view is initially loaded in order to receive user input.
      - If the form is completed,
           - The codeIgniter Form Validation library is used to validate the form data that has been submitted.
           - Insert member data in the database.

5. edit() – Using the update() function of the Member model, you may edit and update particular member data.

      - Using the particular ID, retrieve the member data from the database.
      - The pre-filled member data is imported into the form view.

      - If the form is submitted,
           - The codeIgniter Form Validation library is used to validate the form data that has been submitted.
           - In the database, update the member data.

6. delete() – Using the delete() function of the Member model, remove member data from the database.

<?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 database-related tasks are handled by the Member model (Fetch, Add, Edit, and Delete).

1. __construct() – Define the name of the table.
2. getRows() – Fetch the members data from the database using the parameters specified in $params. On success, it returns the filtered records.
3. insert() – Insert member information into the database. If successful, returns the row ID; otherwise, returns FALSE.
4. update() – Based on the row ID, update member data in the database. If the function is successful, it returns TRUE; otherwise, it returns FALSE.

5. delete() – Using the row ID, delete the record from the database. If the function is successful, it returns TRUE; otherwise, it returns FALSE.

<?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 contains the web page components (header, footer, etc.).

templates/header.php

The header portion of the web page is stored in this file. The HTML table and form are styled using the Bootstrap 4 library. Include the Bootstrap library's CSS file as well as 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

The footer section of the web page is stored in this file.

</body>
</html>

members/

The views/members/ directory contains the Members controller's view files.

members/index.php

Initially, all of the members' data is fetched from the database and shown on the homepage with links to Add, Edit, and Delete.

 - The search option is now available in the CRUD data list. The search input area allows the user to filter and sort the members list.
 - Using the Pagination class's create links() method, the pagination links are added to the bottom of the CRUD data list.
 - The View link takes the user to the members/view page, where they may execute a Read activity.
 - The Add link takes the user to the members/add page, where they may conduct the Create activity.
 - The Edit link takes the user to the members/edit page, where they may complete the Update procedure.
 - The Delete link takes the user to the members/delete page, where they may complete the Delete function.

<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

The view() function of the members controller loads this view. The Bootstrap card view displays the particular member details.

<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
The members controller's add() and edit() methods load this view.

 - On add suggestion, an HTML form is showcased to obtain the user’s input (name, edit, sex, nation, etc).
 - When an update request is made, an HTML form with pre-filled member data in the input fields is presented.

<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

The index.php file is included in the URLs of CRUD operation requests by default. To remove index.php from CodeIgniter URLs, follow the instructions below.

1. Remove index.php from the index page variable in the config/config.php file and leave it blank.

$config['index_page'] = '';

In the CodeIgniter root directory, create an HTACCESS (.htaccess) file and enter the following URL. Rule code should be rewritten.

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

Please keep in mind that the mod rewrite extension must be enabled on your server. You must give the path to your application's location (RewriteRule (.*)$ /folder name/index.php/$1 [L]).

Conclusion

Our CodeIgniter and MySQL sample CRUD application will assist you in implementing data management (fetch, insert, update, and delete) functions in the CodeIgniter framework. This example script adds pagination and search functionality to the CodeIgniter CRUD data list to make it more user-friendly. Because we utilised the Bootstrap toolkit to style the form and table, just a small amount of CSS is necessary to generate the list and form views. You may also simply extend the CodeIgniter CRUD with search and pagination script features to meet your specific needs.

LICENSE OF USE

You can use it for personal or commercial projects. You can't resell it partially or in this form.

PRODUCT INFO

Create Date : Feb 15, 2022

Updated Date : Feb 15, 2022

Ratings

Comments : 0

Downloads : 0