Inline Table CRUD in Codeigniter using Ajax jquery

If you are beginner Codeigniter web developer then this tutorial will help you to learn something new in Codeigniter using Ajax. Because in this post we have covered topic like Live table Add Edit Delete Mysql records in Codeigniter using Ajax. We all know what is Live table or inline table, this is simple table grid which has been use not only for display data on web page but also in this table grid we can perform all CRUD operation like Insert, Update, Delete and Read mysql table data using Ajax. If you are regular reader of our blog then you have seen many tutorial on Live table CRUD in PHP using Ajax jQuery. So this type of feature we will make in Codeigniter framework. Because now most of the PHP developer has use any framework for their web development. So, we have publish this tutorial for Codeigniter beginner web developer who want to learn some advance topic in this framework with Ajax jQuery.

Live table or Inline table help user to perform their work faster because all work has been done on single page without going to another page and all server side operation has been done back end without refresh of web page. For Create new records he can do this task in table without open any open form and same as suppose user want to edit or update any records then he can also do this operation in table because all table column has been editable and user want to edit then he simply change data and data will be edited and for delete data also he has just click on delete button and records will be deleted. So all CRUD operation has been done on single page and all operation has been done without refresh of web page. So this all things we will done in Codeigniter framework using Ajax jQuery.

View Demo

Create Database

By using following sql script you can create table in your mysql database. For this application you have to first create mysql table for all CRUD operation using Ajax in Codeigniter application.

--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `sample_data`
--

CREATE TABLE `sample_data` (
  `id` int(10) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `age` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `sample_data`
--
ALTER TABLE `sample_data`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Set Base Url

First in Codeigniter framework we have to first set base url of your application. This is root of your application and this url will run your main controller which you have set in route.php

application/config/config.php

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

$config['base_url'] = 'http://localhost/tutorial/codeigniter-live-table-add-edit-delete-using-ajax/';

?>

Make Database Connection

In Codeigniter for make mysql database connection we have to go to application/config/database.php and under this page we have to define database details like host name, database name, username and password details. By defining this details it will make database connection in Codeigniter.

application/config/database.php

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

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root',
 'password' => '',
 'database' => 'testing',
 'dbdriver' => 'mysqli',
 'dbprefix' => '',
 'pconnect' => FALSE,
 'db_debug' => (ENVIRONMENT !== 'production'),
 'cache_on' => FALSE,
 'cachedir' => '',
 'char_set' => 'utf8',
 'dbcollat' => 'utf8_general_ci',
 'swap_pre' => '',
 'encrypt' => FALSE,
 'compress' => FALSE,
 'stricton' => FALSE,
 'failover' => array(),
 'save_queries' => TRUE
);

?>

Create Controller

Now our application core part has been started and first we want to make controller with name like LiveTable.php in Codeigniter framework. In this controller we have make following function.

__construct() - This is magic function and it will be executed when new object of this Class has been created and it will load livetable_model class. So we do not want to load again and again livetable_model in our application.

index() - This is root function of this class, so when in browser we have type base url with this controller name then it will execute this function and it will load live_table view file.

load_data() - This function will received ajax request for read all data from mysql table and return data in json format using json_encode() function.

index() - This function is use for create or add or insert new data into Mysql table and for this operation this function will received Ajax request.

update() - Controller of this function will be executed if any edit or update data request then this function will received ajax request from view for edit mysql data.

delete() - This is last function of Controller will use for delete particular data from Mysql table and this function will also execute when it has received ajax request.

application/controllers/LiveTable.php

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

class LiveTable extends CI_Controller {

 public function __construct()
 {
  parent::__construct();
  $this->load->model('livetable_model');
 }

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

 function load_data()
 {
  $data = $this->livetable_model->load_data();
  echo json_encode($data);
 }

 function insert()
 {
  $data = array(
   'first_name' => $this->input->post('first_name'),
   'last_name'  => $this->input->post('last_name'),
   'age'   => $this->input->post('age')
  );

  $this->livetable_model->insert($data);
 }

 function update()
 {
  $data = array(
   $this->input->post('table_column') => $this->input->post('value')
  );

  $this->livetable_model->update($data, $this->input->post('id'));
 }

 function delete()
 {
  $this->livetable_model->delete($this->input->post('id'));
 }
 

}

?>

Create Model

In Codeigniter framework Model has been used from perform all database operation like Insert Update Delete and Fetch data from database and in this Class function will be executed from Controllers method. Here we have make LiveTable_model.php and in this we have make following function in this Model.

load_data() - This function fetch data from Mysql database and return result in Array format by using result_array() function.

insert($data) - For Insert new record into Mysql database this function has been use and this function will received $data argument data get from LiveTable controller insert() method.

update($data, $id) - For Update or Edit any existing data in Mysql this function has been used and under this function $data and $id value has been pass from Controller function.

delete($id) - Same way for Delete data operation this function has been used and this function will received $id argument value from Controller function.

application/models/LiveTable_model.php

<?php
class LiveTable_model extends CI_Model
{
 function load_data()
 {
  $this->db->order_by('id', 'DESC');
  $query = $this->db->get('sample_data');
  return $query->result_array();
 }

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

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

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

Create View

In Controller View file has been used for display HTML output in browser, we all see html output data on web page using this View file. So here also we have create view file under application/views/live_table.php. In this file we have use jQuery and Bootstrap library. Here we have make Live table grid, so here we have created one html and all data has been load under tag of table. In table we can edit table content by using contenteditable attribute. For all CRUD operation here we have use Ajax. In below code you can find complete source code of view file.

<html>
<head>
    <title>Codeigniter Live Table Add Edit Delete using Ajax</title>
    
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
    <style>
    body
    {
      margin:0;
      padding:0;
      background-color:#f1f1f1;
    }
    .box
    {
      width:900px;
      padding:20px;
      background-color:#fff;
      border:1px solid #ccc;
      border-radius:5px;
      margin-top:10px;
    }
  </style>
</head>
<body>
  <div class="container box">
    <h3 align="center">Codeigniter Live Table Add Edit Delete using Ajax</h3><br />
    <div class="table-responsive">
      <br />
      <table class="table table-striped table-bordered">
        <thead>
          <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Age</th>
            <th>Action</th>
          </tr>
        </thead>
        <tbody>
        </tbody>
      </table>   
    </div>
  </div>
</body>
</html>

<script type="text/javascript" language="javascript" >
$(document).ready(function(){
  
  function load_data()
  {
    $.ajax({
      url:"<?php echo base_url(); ?>livetable/load_data",
      dataType:"JSON",
      success:function(data){
        var html = '<tr>';
        html += '<td id="first_name" contenteditable placeholder="Enter First Name"></td>';
        html += '<td id="last_name" contenteditable placeholder="Enter Last Name"></td>';
        html += '<td id="age" contenteditable></td>';
        html += '<td><button type="button" name="btn_add" id="btn_add" class="btn btn-xs btn-success"><span class="glyphicon glyphicon-plus"></span></button></td></tr>';
        for(var count = 0; count < data.length; count++)
        {
          html += '<tr>';
          html += '<td class="table_data" data-row_id="'+data[count].id+'" data-column_name="first_name" contenteditable>'+data[count].first_name+'</td>';
          html += '<td class="table_data" data-row_id="'+data[count].id+'" data-column_name="last_name" contenteditable>'+data[count].last_name+'</td>';
          html += '<td class="table_data" data-row_id="'+data[count].id+'" data-column_name="age" contenteditable>'+data[count].age+'</td>';
          html += '<td><button type="button" name="delete_btn" id="'+data[count].id+'" class="btn btn-xs btn-danger btn_delete"><span class="glyphicon glyphicon-remove"></span></button></td></tr>';
        }
        $('tbody').html(html);
      }
    });
  }

  load_data();

  $(document).on('click', '#btn_add', function(){
    var first_name = $('#first_name').text();
    var last_name = $('#last_name').text();
    var age = $('#age').text();
    if(first_name == '')
    {
      alert('Enter First Name');
      return false;
    }
    if(last_name == '')
    {
      alert('Enter Last Name');
      return false;
    }
    $.ajax({
      url:"<?php echo base_url(); ?>livetable/insert",
      method:"POST",
      data:{first_name:first_name, last_name:last_name, age:age},
      success:function(data){
        load_data();
      }
    })
  });

  $(document).on('blur', '.table_data', function(){
    var id = $(this).data('row_id');
    var table_column = $(this).data('column_name');
    var value = $(this).text();
    $.ajax({
      url:"<?php echo base_url(); ?>livetable/update",
      method:"POST",
      data:{id:id, table_column:table_column, value:value},
      success:function(data)
      {
        load_data();
      }
    })
  });

  $(document).on('click', '.btn_delete', function(){
    var id = $(this).attr('id');
    if(confirm("Are you sure you want to delete this?"))
    {
      $.ajax({
        url:"<?php echo base_url(); ?>livetable/delete",
        method:"POST",
        data:{id:id},
        success:function(data){
          load_data();
        }
      })
    }
  });
  
});
</script>

By using above source code we can make Live table or Inline Table Crud Single page application in Codeigniter using Ajax which help user to perform their task easily without going to other page. If you want to download complete source code of this tutorial click on below link.

Download Source Code