CodeIgniter CRUD Operations without Page Refresh using jQuery and Ajax

CRUD Operations in CodeIgniter are the most implemented functionality for the web application. CodeIgniter CRUD (Create, Read, Update and Delete) operations are used to manipulate data (Fetch, Insert, Update, and Delete) in the database. Generally, the page is refreshed and redirected when an action is requested in CodeIgniter CRUD application. Also, the CRUD operations are also be implemented without page refresh in CodeIgniter using jQuery and Ajax.

If you want to make the CodeIgniter CRUD feature user-friendly, integrate this functionality without page reload. In this tutorial, we will show you how to implement CRUD functionality in CodeIgniter without page refresh using jQuery and Ajax. The example script helps you to integrate data management (view, add, edit, and delete) functionality in CodeIgniter 3 framework using jQuery, Ajax, and MySQL.

The following functionality will be implemented to build CodeIgniter CRUD application with Bootstrap 4 using jQuery, Ajax, and MySQL.

• Fetch the members data from the database and listed on the web page.
• Add member data to the database.
• Edit and update member data in the database.
• Delete member data from the database.
• The add/edit/delete operations will work on a single page without page refresh.

Before getting started to create CodeIgniter AJAX CRUD application, take a look the files structure.

codeigniter_ajax_crud/
├── application/
│   ├── controllers/
│   │   └── Members.php
│   ├── models/
│   │   └── Member.php
│   └── views/
│       ├── templates/
│       │   ├── header.php
│       │   └── footer.php
│       └── members/
│           ├── index.php
│           └── view.php
└── assets/
    ├── css/
    ├── js/
    ├── images/
    └── bootstrap/

Create Database Table

To store and manage the data a table needs to be created in the database. The following SQL creates a members table with some basic fields 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 commonly used library and helper to load automatically on every request.

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

Controller (Members.php)

The Members controller handles the CRUD operations (view, add, edit, and delete) based on the request coming via AJAX POST.

• __construct() – Loads the Member model to handle the database related works.
• index() – Fetch all member records from the database based using getRows() method and pass data to the view for listing.
• memData() – Fetch a single member data from the database based on their ID. Returns the member data as JSON format.
• listView() – Fetch all members data from the database. Returns the data as HTML format.
• add() –
  • Retrieve the form fields data posted via AJAX request.
  • Validate the form fields input.
  • Insert the member data in the database using insert() method of Member model.
  • Returns the response to AJAX as JSON format.
• edit() –
  • Retrieve and validate the form fields data posted via AJAX request.
  • Update member data in the database based on the ID using update() method of Member model.
  • Returns the response to AJAX as JSON format.
• delete() –
  • Remove member data from the database based on the ID posted by AJAX request.
  • Returns the status as JSON format.

<?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');
    }
    
    public function index(){
        $data = array();
        
        // Get rows count
        $conditions['returnType']     = 'count';
        $rowsCount = $this->member->getRows($conditions);
        
        // Get rows
        $conditions['returnType'] = '';
        $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 memData(){
        $id = $this->input->post('id');
        if(!empty($id)){
            // Fetch member data
            $member = $this->member->getRows(array('id'=>$id));
            
            // Return data as JSON format
            echo json_encode($member);
        }
    }

    public function listView(){
        $data = array();
        
        // Fetch all records
        $data['members'] = $this->member->getRows();
            
        // Load the list view
        $this->load->view('members/view', $data);
    }
    
    public function add(){
        $verr = $status = 0;
        $msg = '';
        $memData = array();
        
        // Get user's input
        $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 form fields
        if(empty($first_name) || empty($last_name)){
            $verr = 1;
            $msg .= 'Please enter your name.<br/>';
        }
        if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){
            $verr = 1;
            $msg .= 'Please enter a valid email.<br/>';
        }
        if(empty($country)){
            $verr = 1;
            $msg .= 'Please enter your country.<br/>';
        }
        
        if($verr == 0){
            // Prepare member data
            $memData = array(
                'first_name'=> $first_name,
                'last_name'    => $last_name,
                'email'        => $email,
                'gender'    => $gender,
                'country'    => $country
            );
            
            // Insert member data
            $insert = $this->member->insert($memData);
            
            if($insert){
                $status = 1;
                $msg .= 'Member has been added successfully.';
            }else{
                $msg .= 'Some problem occurred, please try again.';
            }
        }
        
        // Return response as JSON format
        $alertType = ($status == 1)?'alert-success':'alert-danger';
        $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);
    }
    
    public function edit(){
        $verr = $status = 0;
        $msg = '';
        $memData = array();
        
        $id = $this->input->post('id');
        
        if(!empty($id)){
            // Get user's input
            $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 form fields
            if(empty($first_name) || empty($last_name)){
                $verr = 1;
                $msg .= 'Please enter your name.<br/>';
            }
            if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){
                $verr = 1;
                $msg .= 'Please enter a valid email.<br/>';
            }
            if(empty($country)){
                $verr = 1;
                $msg .= 'Please enter your country.<br/>';
            }
            
            if($verr == 0){
                // Prepare member data
                $memData = array(
                    'first_name'=> $first_name,
                    'last_name'    => $last_name,
                    'email'        => $email,
                    'gender'    => $gender,
                    'country'    => $country
                );
                
                // Update member data
                $update = $this->member->update($memData, $id);
                
                if($update){
                    $status = 1;
                    $msg .= 'Member has been updated successfully.';
                }else{
                    $msg .= 'Some problem occurred, please try again.';
                }
            }
        }else{
            $msg .= 'Some problem occurred, please try again.';
        }
        
        // Return response as JSON format
        $alertType = ($status == 1)?'alert-success':'alert-danger';
        $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);
    }
    
    public function delete(){
        $msg = '';
        $status = 0;
        
        $id = $this->input->post('id');
        
        // Check whether member id is not empty
        if(!empty($id)){
            // Delete member
            $delete = $this->member->delete($id);
            
            if($delete){
                $status = 1;
                $msg .= 'Member has been removed successfully.';
            }else{
                $msg .= 'Some problem occurred, please try again.';
            }
        }else{
            $msg .= 'Some problem occurred, please try again.';
        }  
        
        // Return response as JSON format
        $alertType = ($status == 1)?'alert-success':'alert-danger';
        $statusMsg = '<p class="alert '.$alertType.'">'.$msg.'</p>';
        $response = array(
            'status' => $status,
            'msg' => $statusMsg
        );
        echo json_encode($response);
    }
}

Model (Member.php)

The Member model handles the database related operations (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 data array on success, and FALSE on error.
• 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(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

1. templates/
The views/templates/ directory holds the element parts (header, footer, etc.) of the web page.

1.1. templates/header.php
The header.php file holds the header part of the web page.

• The jQuery and Ajax are used to handle CRUD operations without page refresh in CodeIgniter, so, include the jQuery library.
• The Bootstrap is used to add modal dialog popup on the web page, and styling the data table, form fields, and links. So, include the CSS & JS library files of the Bootstrap 4.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title><?php echo $title; ?> | CodeIgniter CRUD without Page Refresh</title>
    
    <!-- jQuery library -->
    <script src="<?php echo base_url('assets/js/jquery.min.js'); ?>"></script>
    
    <!-- Bootstrap library -->
    <link rel="stylesheet" href="<?php echo base_url('assets/bootstrap/bootstrap.min.css'); ?>">
    <script src="<?php echo base_url('assets/bootstrap/bootstrap.min.js'); ?>"></script>
    
    <!-- Stylesheet file -->
    <link rel="stylesheet" href="<?php echo base_url('assets/css/style.css'); ?>">
</head>
<body>

1.2. templates/footer.php
The footer.php file holds the footer part of the web page.

</body>
</html>

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

2.1. members/index.php
This view lists all the member’s data in a tabular format and handles the CRUD (View, Add, Edit/Update, and Delete) requests using jQuery and Ajax.

HTML Code:
Initially, the member’s data is fetched from the database and listed in the HTML table with the Edit & Delete links.

• At the top of the data list, an Add link is placed to initiate the create request.
• On clicking the Add button, the Bootstrap modal dialog appears with the HTML form to add a new member.
• On clicking the Edit button, the Bootstrap modal dialog appears with the HTML form and pre-filled data to update member’s data.
• On clicking the Delete button, a confirmation dialog appears. After the confirmation, delete request is initiated to remove the record from the database.

<div class="container">
    <!-- Display status message -->
    <div class="statusMsg"></div>
    
    <div class="row">
        <div class="col-md-12 head">
            <h5><?php echo $title; ?></h5>
            <!-- Add link -->
            <div class="float-right">
                <a href="javascript:void(0);" class="btn btn-success" data-type="add" data-toggle="modal" data-target="#modalUserAddEdit"><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 id="userData">
            <?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="javascript:void(0);" class="btn btn-warning" rowID="<?php echo $row['id']; ?>" data-type="edit" data-toggle="modal" data-target="#modalUserAddEdit">edit</a>
                    <a href="javascript:void(0);" class="btn btn-danger" onclick="return confirm('Are you sure to delete data?')?userAction('delete', '<?php echo $row['id']; ?>'):false;">delete</a>
                </td>
            </tr>
            <?php } }else{ ?>
            <tr><td colspan="7">No member(s) found...</td></tr>
            <?php } ?>
        </tbody>
        </table>
    </div>
</div>


<!-- Modal Add and Edit Form -->
<div class="modal fade" id="modalUserAddEdit" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">
            <!-- Modal Header -->
            <div class="modal-header">
                <h4 class="modal-title"><span id="hlabel">Add New</span> Member</h4>
                <button type="button" class="close" data-dismiss="modal">&times;</button>
            </div>
            
            <!-- Modal Body -->
            <div class="modal-body">
                <div class="statusMsg"></div>
                <form role="form">
                    <div class="form-group">
                        <label>First name</label>
                        <input type="text" class="form-control" name="first_name" id="first_name" placeholder="Enter first name" >
                    </div>
                    <div class="form-group">
                        <label>Last name</label>
                        <input type="text" class="form-control" name="last_name" id="last_name" placeholder="Enter last name" >
                    </div>
                    <div class="form-group">
                        <label>Email</label>
                        <input type="text" class="form-control" name="email" id="email" placeholder="Enter email" >
                    </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" 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" >
                            <label class="custom-control-label" for="gender2">Female</label>
                        </div>
                    </div>
                    <div class="form-group">
                        <label>Country</label>
                        <input type="text" class="form-control" name="country" id="country" placeholder="Enter country" >
                    </div>
                    <input type="hidden" class="form-control" name="id" id="id"/>
                </form>
            </div>
            
            <!-- Modal Footer -->
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                <button type="button" class="btn btn-success" id="userSubmit">SUBMIT</button>
            </div>
        </div>
    </div>
</div>

JavaScript Code:
The following JavaScript code handles the Ajax requests with CodeIgniter application.

• getUsers() – Retrieve the members data from the Members controller (members/listView) using jQuery $.post() method. On success, the member data list content is changed with the response data.
• userAction() – Send add, edit, and delete request to Members controller (members/add | members/edit | members/delete) using jQuery and Ajax. On success, the response message is shown to the user.
• editUser() – Retrieve a specific member data from the Members controller (members/memData) using jQuery and Ajax. On success, set the respective value in the form fields.
• shown.bs.modal – This Bootstrap modal event is used to change the data-type attribute value based on the add/edit request.
  If Add request is initiated, the userAction() is set to the onclick attribute of the submit button (#userSubmit) with add param.If Edit request is initiated, the userAction() is set to the onclick attribute of the submit button (#userSubmit) with edit param. Also, the editUser() function is called to pre-fill the member’s data in the modal form based on the rowID.
• hidden.bs.modal – This Bootstrap modal event is used to remove the onclick attribute from the submit button (#userSubmit). Also, the pre-filled form data and the status message is removed from the modal form and web page.

<script>
// Update the members data list
function getUsers(){
    $.post( "<?php echo base_url('members/listView/'); ?>", function( data ){
        $('#userData').html(data);
    });
}

// Send CRUD requests to the server-side script
function userAction(type, id){
    id = (typeof id == "undefined")?'':id;
    var userData = '', frmElement = '';
    if(type == 'add'){
        frmElement = $("#modalUserAddEdit");
        userData = frmElement.find('form').serialize();
    }else if (type == 'edit'){
        frmElement = $("#modalUserAddEdit");
        userData = frmElement.find('form').serialize();
    }else{
        frmElement = $(".row");
        userData = 'id='+id;
    }
    frmElement.find('.statusMsg').html('');
    $.ajax({
        type: 'POST',
        url: '<?php echo base_url('members/'); ?>'+type,
        dataType: 'JSON',
        data: userData,
        beforeSend: function(){
            frmElement.find('form').css("opacity", "0.5");
        },
        success:function(resp){
            frmElement.find('.statusMsg').html(resp.msg);
            if(resp.status == 1){
                if(type == 'add'){
                    frmElement.find('form')[0].reset();
                }
                getUsers();
            }
            frmElement.find('form').css("opacity", "");
        }
    });
}

// Fill the user's data in the edit form
function editUser(id){
    $.post( "<?php echo base_url('members/memData/'); ?>", {id: id}, function( data ){
        $('#id').val(data.id);
        $('#first_name').val(data.first_name);
        $('#last_name').val(data.last_name);
        $('#email').val(data.email);
        $('input:radio[name="gender"]').filter('[value="'+data.gender+'"]').attr('checked', true);
        $('#country').val(data.country);
    }, "json");
}

// Actions on modal show and hidden events
$(function(){
    $('#modalUserAddEdit').on('show.bs.modal', function(e){
        var type = $(e.relatedTarget).attr('data-type');
        var userFunc = "userAction('add');";
        $('#hlabel').text('Add New');
        if(type == 'edit'){
            userFunc = "userAction('edit');";
            var rowId = $(e.relatedTarget).attr('rowID');
            editUser(rowId);
            $('#hlabel').text('Edit');
        }
        $('#userSubmit').attr("onclick", userFunc);
    });
    
    $('#modalUserAddEdit').on('hidden.bs.modal', function(){
        $('#userSubmit').attr("onclick", "");
        $(this).find('form')[0].reset();
        $(this).find('.statusMsg').html('');
    });
});
</script>

2.2. members/view.php
This view is used by the listView() method of Members controller, lists the members data in HTML format.

<?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="javascript:void(0);" class="btn btn-warning" rowID="<?php echo $row['id']; ?>" data-type="edit" data-toggle="modal" data-target="#modalUserAddEdit">edit</a>
        <a href="javascript:void(0);" class="btn btn-danger" onclick="return confirm('Are you sure to delete data?')?userAction('delete', '<?php echo $row['id']; ?>'):false;">delete</a>
    </td>
</tr>
<?php } }else{ ?>
<tr><td colspan="7">No member(s) found...</td></tr>
<?php } ?>

Conclusion

Our sample AJAX CRUD application with CodeIgniter and MySQL helps you to implement data management functionality in the CodeIgniter framework without page refresh and reload. The user doesn’t need to navigate the pages to manage the data with the database. You can implement the add, edit, update, and delete functionality with the MySQL database using jQuery and Ajax. With the sample Ajax CRUD application in CodeIgniter, the CRUD operations can be easily integrated without page refresh using jQuery.

CodeIgniter CRUD Operations with Search and Pagination