Friday 28 December 2018

Codeigniter Ajax CRUD Application using jQuery Bootgrid

If you have looking for make Single Page CRUD application in Codeigniter using Ajax, then this post will help you. Because in this post you can learn step by step or from scratch how to use jQuery Bootgrid plugin in Codeigniter with Ajax. jQuery Bootgrid plugin is a lightweight plugin, which is flexible, powerful grid plugin for load dynamic data using Ajax, it is also very customizable grid control mainly if you have use Bootstrap library. It is mainly used for display dynamic data using Ajax.


By using this plugin you can get following benifits:


  • Simple Header or Footer Navigation
  • Soriting Table Column Data
  • Live Searching or Filtering of Data
  • Pagination
  • Client Side Processing and Server Side Processing of Data

Once of our previouse post in which we have already covered jQuery Bootgrid Server Side Processing in PHP using Ajax. But here we have seen how to integrate Bootgrid Grid Plugin in Codeigniter Framework. Becase Codeigniter is PHP MVC framework, which is widely used by many web developers for their web development. So, If you are Codeigniter Web Developer then you have to know How to use Bootgrid Plugin with Codeigniter application. For this here we have make this tutorial. In this post we will Covered following CRUD operation with jQuery Bootgrid in Codeigniter using Ajax.


  • Do Server Side Processing and Load Data in jQuery Bootgrid Plugin in Codeigniter using Ajax
  • Add or Insert Data into Mysql in Codeigniter using Ajax with jQuery Bootgrid & Bootstrap Modal
  • Edit or Update Mysql Data in Codeigniter using Ajax with jQuery Bootgrid & Bootstrap Modal
  • Delete or Remove Data from Mysql in Codeigniter using Ajax with jQuery Bootgrid & Bootstrap Modal








Bootgrid.php(Controller)


First We have to make Bootgrid.php Controller under application/controllers folder. This class is mainly used for handle http request for perform Insert, Update, Delete and Read data Request. In this class, you can find following function.


index() - This is root function for this class, when in browser, we have write base url with this class then this function has been called. This function has load bootgrid.php view as output on browser.


fetch_data() - This function is received Ajax request from Bootgrid plugin for fetch data from mysql data. This function will convert data into Array format, and send to Bootgrid Ajax request in json string formate.


action() - This function is used for insert or add into mysql table using ajax. Because this function has received ajax request for insert data into mysql table.


fetch_single_data() - This function mainly fetch particular employee details based on value of employee id. This is for edit existing employee details.

delete_data() - This function is used for handle Ajax delete data request from jQuery Bootgrid Plugin in Codeigniter.



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

class Bootgrid extends CI_Controller {
 
 public function __construct()
 {
  parent::__construct();
  $this->load->model('bootgrid_model');
 }

 function index()
 {
  $this->load->view('bootgrid');
 }

 function fetch_data()
 {
  $data = $this->bootgrid_model->make_query();
  $array = array();
  foreach($data as $row)
  {
   $array[] = $row;
  }
  $output = array(
   'current'  => intval($_POST["current"]),
   'rowCount'  => 10,
   'total'   => intval($this->bootgrid_model->count_all_data()),
   'rows'   => $array
  );
  echo json_encode($output);
 }

 function action()
 {
  if($this->input->post('operation'))
  {
   $data = array(
    'name'   => $this->input->post('name'),
    'address'  => $this->input->post('address'),
    'gender'  => $this->input->post('gender'),
    'designation' => $this->input->post('designation'),
    'age'   => $this->input->post('age')
   );
   if($this->input->post('operation') == 'Add')
   {
    $this->bootgrid_model->insert($data);
    echo 'Data Inserted';
   }
   if($this->input->post('operation') == 'Edit')
   {
    $this->bootgrid_model->update($data, $this->input->post('employee_id'));
    echo 'Data Updated';
   }
  }
 }

 function fetch_single_data()
 {
  if($this->input->post('id'))
  {
   $data = $this->bootgrid_model->fetch_single_data($this->input->post('id'));
   foreach($data as $row)
   {
    $output['name'] = $row['name'];
    $output['address'] = $row['address'];
    $output['gender'] = $row['gender'];
    $output['designation'] = $row['designation'];
    $output['age'] = $row['age'];
   }
   echo json_encode($output);
  }
 }

 function delete_data()
 {
  if($this->input->post('id'))
  {
   $this->bootgrid_model->delete($this->input->post('id'));
   echo 'Data Deleted';
  }
 }
}

?>





Bootgrid_model.php(Models)


This class we have to make under application/models folder. This model class is mainly used for all type of database operation. In this class we have make following function for perform database related operation.


make_query() - This function is for fetch data from mysql table according to requirement for Bootgrid plugin. In this function we have make fetch data select query with Where clause for searching data, order_by clause for sorting data, and limit clause for pagination of data.


count_all_data() - This function return number of rows in employee table.


insert() - This mainly used for insert database operaton. It will make insert query, execute query and insert data into Database.


fetch_single_data() - This function is used for Select data of single employee based on value of $id variable.

update() - This function is for perform update or edit mysql database operation.

delete() - This function is for do mysql delete or remove data operation in Codeigniter.


application/models/Bootgrid_model.php


<?php
class Bootgrid_model extends CI_Model
{
 var $records_per_page = 10;
 var $start_from = 0;
 var $current_page_number = 1;

 function make_query()
 {
  if(isset($_POST["rowCount"]))
  {
   $this->records_per_page = $_POST["rowCount"];
  }
  else
  {
   $this->records_per_page = 10;
  }
  if(isset($_POST["current"]))
  {
   $this->current_page_number = $_POST["current"];
  }
  $this->start_from = ($this->current_page_number - 1) * $this->records_per_page;
  $this->db->select("*");
  $this->db->from("tbl_employee");
  if(!empty($_POST["searchPhrase"]))
  {
   $this->db->like('name', $_POST["searchPhrase"]);
   $this->db->or_like('address', $_POST["searchPhrase"]);
   $this->db->or_like('gender', $_POST["searchPhrase"]);
   $this->db->or_like('designation', $_POST["searchPhrase"]);
   $this->db->or_like('age', $_POST["searchPhrase"]);
  }
  if(isset($_POST["sort"]) && is_array($_POST["sort"]))
  {
   foreach($_POST["sort"] as $key => $value)
   {
    $this->db->order_by($key, $value);
   }
  }
  else
  {
   $this->db->order_by('id', 'DESC');
  }
  if($this->records_per_page != -1)
  {
   $this->db->limit($this->records_per_page, $this->start_from);
  }
  $query = $this->db->get();
  return $query->result_array();
 }

 function count_all_data()
 {
  $this->db->select("*");
  $this->db->from("tbl_employee");
  $query = $this->db->get();
  return $query->num_rows();
 }

 function insert($data)
 {
  $this->db->insert('tbl_employee', $data);
 }

 function fetch_single_data($id)
 {
  $this->db->where('id', $id);
  $query = $this->db->get('tbl_employee');
  return $query->result_array();
 }

 function update($data, $id)
 {
  $this->db->where('id', $id);
  $this->db->update('tbl_employee', $data);
 }

 function delete($id)
 {
  $this->db->where('id', $id);
  $this->db->delete('tbl_employee');
 }
}

?>


bootgrid.php(Views)


View file in Codeigniter framework is used for display HTML output on Web page. This bootgrid.php view file you can find under application/views folder. Here we have make HTML table as per Bootgrid plugin, and below you can find jQuery code, in which we have initialiaze jQuery Bootgrid plugin using bootgrid() method. Under this method we have trigger Ajax request for fetch data, and under this also we have make edit and delete button also under formatters option.



<html>
<head>
    <title>jQuery Bootgrid - Server Side Processing in Codeigniter</title>
    
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.css" />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script> 
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-bootgrid/1.3.1/jquery.bootgrid.js"></script>  
</head>
<body>
    <div class="container box">
        <h3 align="center">jQuery Bootgrid - Server Side Processing in Codeigniter</h3><br />
        <div class="panel panel-default">
            <div class="panel-heading">
                <div class="row">
                    <div class="col-md-10">
                        <h3 class="panel-title">Employee List</h3>
                    </div>
                    <div class="col-md-2" align="right">
                        <button type="button" id="add_button" data-toggle="modal" data-target="#employeeModal" class="btn btn-info btn-xs">Add</button>
                    </div>
                </div>
                
            </div>
            <div class="panel-body">
                <div class="table-responsive">
                    <table id="employee_data" class="table table-striped table-bordered">
                        <thead>
                            <tr>
                                <th data-column-id="name">Name</th>
                                <th data-column-id="address">Address</th>
                                <th data-column-id="gender">Gender</th>
                                <th data-column-id="designation">Designation</th>
                                <th data-column-id="age">Age</th>
                                <th data-column-id="commands" data-formatter="commands" data-sortable="false">Action</th>
                            </tr>
                        </thead>
                    </table>
                </div>
            </div>
       </div>
    </div>
</body>
</html>

<div id="employeeModal" class="modal fade">
    <div class="modal-dialog">
        <form method="post" id="employee_form">
            <div class="modal-content">
                <div class="modal-header">
                    <button type="button" class="close" data-dismiss="modal">&times;</button>
                    <h4 class="modal-title">Add Employee</h4>
                </div>
                <div class="modal-body">
                    <div class="form-group">
                        <label>Enter Name</label>
                        <input type="text" name="name" id="name" class="form-control" />
                    </div>
                    <div class="form-group">
                        <label>Enter Address</label>
                        <textarea name="address" id="address" class="form-control"></textarea>
                    </div>
                    <div class="form-group">
                        <label>Select Gender</label>
                        <select name="gender" id="gender" class="form-control">
                            <option value="Male">Male</option>
                            <option value="Female">Female</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <label>Enter Designation</label>
                        <input type="text" name="designation" id="designation" class="form-control" />
                    </div>
                    <div class="form-group">
                        <label>Enter Age</label>
                        <input type="text" name="age" id="age" class="form-control" />
                    </div>
                </div>
                <div class="modal-footer">
                    <input type="hidden" name="employee_id" id="employee_id" />
                    <input type="hidden" name="operation" id="operation" value="Add" />
                    <input type="submit" name="action" id="action" class="btn btn-success" value="Add" />
                </div>
            </div>
        </form>
    </div>
</div>

<script type="text/javascript" language="javascript" >
$(document).ready(function(){
    
    var employeeTable = $('#employee_data').bootgrid({
        ajax:true,
        rowSelect: true,
        post:function()
        {
            return{
                id:"b0df282a-0d67-40e5-8558-c9e93b7befed"
            }
        },
        url:"<?php echo base_url(); ?>bootgrid/fetch_data",
        formatters:{
            "commands":function(column, row)
            {
                return "<button type='button' class='btn btn-warning btn-xs update' data-row-id='"+row.id+"'>Edit</button>" + "&nbsp; <button type='button' class='btn btn-danger btn-xs delete' data-row-id='"+row.id+"'>Delete</button>";
            }
        }
    });

    $('#add_button').click(function(){
        $('#employee_form')[0].reset();
        $('.modal-title').text("Add Employee");
        $('#action').val("Add");
        $('#operation').val("Add");
    });

    $(document).on('submit', '#employee_form', function(event){
        event.preventDefault();
        var name = $('#name').val();
        var address = $('#address').val();
        var gender = $('#gender').val();
        var designation = $('#designation').val();
        var age = $('#age').val();
        var form_data = $(this).serialize();
        if(name != '' && address != '' &&  gender != '' &&  designation != '' && age != '')
        {
            $.ajax({
                url:"<?php echo base_url(); ?>bootgrid/action",
                method:"POST",
                data:form_data,
                success:function(data)
                {
                    alert(data);
                    $('#employee_form')[0].reset();
                    $('#employeeModal').modal('hide');
                    $('#employee_data').bootgrid('reload');
                }
            });
        }
        else
        {
            alert("All Fields are Required");
        }
    });

    $(document).on("loaded.rs.jquery.bootgrid", function(){
        employeeTable.find('.update').on('click', function(event){
            var id = $(this).data('row-id');
            $.ajax({
                url:"<?php echo base_url(); ?>bootgrid/fetch_single_data",
                method:"POST",
                data:{id:id},
                dataType:"json",
                success:function(data)
                {
                    $('#employeeModal').modal('show');
                    $('#name').val(data.name);
                    $('#address').val(data.address);
                    $('#gender').val(data.gender);
                    $('#designation').val(data.designation);
                    $('#age').val(data.age);
                    $('.modal-title').text("Edit Employee Details");
                    $('#employee_id').val(id);
                    $('#action').val('Edit');
                    $('#operation').val('Edit');
                }
            });
        });

        employeeTable.find('.delete').on('click', function(event){
            if(confirm("Are you sure you want to delete this?"))
            {
                var id = $(this).data('row-id');
                $.ajax({
                    url:"<?php echo base_url(); ?>bootgrid/delete_data",
                    method:"POST",
                    data:{id:id},
                    success:function(data)
                    {
                        alert(data);
                        $('#employee_data').bootgrid('reload');
                    }
                });
            }
            else
            {
                return false;
            }
        });
    });
    
});
</script>


By using this post, you will make Codeigniter Ajax Single Page Application using jQeury Bootgrid plugin.

7 comments:

  1. help me please!
    what is the name of database this tutorial?

    ReplyDelete
    Replies
    1. tbl_employee . You can give any database name . just added that database into application/config/database

      Delete
  2. help me please!
    what is the name of database this tutorial?

    ReplyDelete
  3. --
    -- Table structure for table `tbl_employee`
    --

    CREATE TABLE `tbl_employee` (
    `id` int(10) NOT NULL,
    `name` varchar(255) NOT NULL,
    `address` varchar(255) NOT NULL,
    `age` int(3) NOT NULL,
    `gender` enum('male','female') NOT NULL,
    `destination` varchar(127) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `tbl_employee`
    --

    INSERT INTO `tbl_employee` (`id`, `name`, `address`, `age`, `gender`, `destination`) VALUES
    (3, 'Tiny', 'Marry', 19, 'female', ''),
    (4, 'Dolores', 'Brooks', 29, 'female', ''),
    (5, 'Cindy', 'Dahl', 24, 'female', ''),
    (6, 'George', 'Fagan', 30, 'male', ''),
    (7, 'Chelsea', 'Mendoza', 18, 'female', ''),
    (8, 'Wayne', 'Hodges', 27, 'male', ''),
    (9, 'Keith', 'Watkin', 26, 'male', ''),
    (10, 'Eric', 'Smith', 31, 'male', ''),
    (11, 'Robert', 'Owens', 42, 'male', ''),
    (12, 'Candace', 'Hand', 27, 'female', ''),
    (13, 'Hortencia', 'Bell', 30, 'female', ''),
    (14, 'William', 'Sosa', 36, 'male', ''),
    (15, 'Patricia', 'Davis', 23, 'female', ''),
    (17, 'Nancy', 'Sedlacek', 215, 'male', '');

    --
    -- Indexes for dumped tables
    --

    --
    -- Indexes for table `tbl_employee`
    --
    ALTER TABLE `tbl_employee`
    ADD PRIMARY KEY (`id`);

    --
    -- AUTO_INCREMENT for dumped tables
    --

    --
    -- AUTO_INCREMENT for table `tbl_employee`
    --
    ALTER TABLE `tbl_employee`
    MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;
    COMMIT;

    ReplyDelete
  4. jquery.min.js:4 POST http://localhost/crudAjax/bootgrid/fetch_data 404 (Not Found)

    ReplyDelete