Live Editable table with jQuery AJAX in CodeIgniter 3

With Live editable table, user can view and edit the list of data on the same page.

You don’t need to send the user to a new page or form for editing the existing record.

In this tutorial, I show how you make live data editing in the HTML table using jQuery AJAX in CodeIgniter 3.

Live Editable table with jQuery AJAX in CodeIgniter 3


Contents

  1. Table structure
  2. Configuration
  3. Model
  4. Controller
  5. View
  6. Demo
  7. Conclusion

1. Table structure

In this example, I am using users table.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configuration

Navigate to application/config/database.php and define Database connection.

$db['default'] = array(
 'dsn' => '',
 'hostname' => 'localhost',
 'username' => 'root', // Username
 'password' => '', // Password
 'database' => 'tutorial', // Database name
 '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
);

Default controller

Open application/config/routes.php and edit default_controller value to Posts.

$route['default_controller'] = 'Users';

Load Database

To access the MySQL database require loading database library.

Open application/config/autoload.php and add the database in libraries array().

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

3. Model

Create a UsersModel.php file in application/models/ directory.

Here, create 2 methods –

  • getUsers() – Using this method fetch all records from the users table and return in an Array format.
  • updateUser() – This method takes 3 parameters – update id, field name, and new value.

Initialize $data Array with $field=>$value. Set where clause and execute update query.

Completed Code

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

class UsersModel extends CI_Model {

  // Fetch users
  function getUsers(){

    // Fetch users
    $this->db->select('*');
    $fetched_records = $this->db->get('users');
    $users = $fetched_records->result_array();

    return $users;
  }

  // Update record
  function updateUser($id,$field,$value){

    // Update
    $data=array($field => $value);
    $this->db->where('id',$id);
    $this->db->update('users',$data);
  }

}

4. Controller

Create a Users.php file in application/controllers/ directory.

Here, create 3 methods –

  • __construct() – Load UsersModel model and url helper.
  • index() – Get all users list by calling getUsers() method and assign in $data['userlist']. Load user_view view and pass $data Array.
  • updateUser() – This method is used to handle AJAX request. Read POST values and pass in updateUser() method to update record.

Completed Code

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

class Users extends CI_Controller {

   public function __construct() {

     parent::__construct();

     // load model
     $this->load->model('UsersModel');

     // load base_url
     $this->load->helper('url');

   }

   public function index(){

     // get data
     $data['userlist'] = $this->UsersModel->getUsers();

     $this->load->view('user_view',$data);
   }

   public function updateuser(){
     // POST values
     $id = $this->input->post('id');
     $field = $this->input->post('field');
     $value = $this->input->post('value');

     // Update records
     $this->UsersModel->updateUser($id,$field,$value);

     echo 1;
     exit;
   }

}

5. View

Create a user_view.php file in application/views/ directory.

Include the jQuery library in <head> section.

Create <table > to list user records.

Loop on the $userlist Array and add new <tr >. With <span class='edit' > display value and also add a textbox.

Use data attributes in the textbox to store editing field name and id.

Script

When <span class='edit' > element gets clicked then hide all textbox elements and show next textbox for edit. Hide the clicked <span >.

When focusout from textbox after editing then read the value of an element, edit id, and field name from data attribute.

Hide the textbox element and update the text of <span > and display it.

Send AJAX POST request to '<?= base_url() ?>index.php/users/updateuser', pass edit id, field name, and value as data.

Completed Code

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!DOCTYPE html>
<html lang="en">
   <head>
     <meta charset="utf-8">
     <title>Live editable table with jQuery AJAX in CodeIgniter 3</title>

     <!-- jQuery library -->
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

     <style type="text/css">
     .txtedit{
        display: none;
        width: 98%;
     }
     </style>
   </head>
   <body>

     <table width='100%' border='1' style='border-collapse: collapse;'>
       <thead>
         <tr>
           <th width='50%'>Name</th>
           <th width='50%'>Email</th>
         </tr>
       </thead>
       <tbody>
       <?php 
       // User List
       foreach($userlist as $user){
         $id = $user['id'];
         $name = $user['name'];
         $email = $user['email'];

         echo "<tr>";
         echo "<td>
         <span class='edit' >".$name."</span>
         <input type='text' class='txtedit' data-id='".$id."' data-field='name' id='nametxt_".$id."' value='".$name."' >
         </td>";
         echo "<td>
         <span class='edit' >".$email."</span>
         <input type='text' class='txtedit' data-id='".$id."' data-field='email' id='emailtxt_".$id."' value='".$email."' >
         </td>";
         echo "</tr>";
       }
       ?>
       </tbody>
     </table>

     <!-- Script -->
     <script type="text/javascript">
     $(document).ready(function(){

       // On text click
       $('.edit').click(function(){
          // Hide input element
          $('.txtedit').hide();

          // Show next input element
          $(this).next('.txtedit').show().focus();

          // Hide clicked element
          $(this).hide();
       });

       // Focus out from a textbox
       $('.txtedit').focusout(function(){
          // Get edit id, field name and value
          var edit_id = $(this).data('id');
          var fieldname = $(this).data('field');
          var value = $(this).val();

          // assign instance to element variable
          var element = this;

          // Send AJAX request
          $.ajax({
            url: '<?= base_url() ?>index.php/users/updateuser',
            type: 'post',
            data: { field:fieldname, value:value, id:edit_id },
            success:function(response){

              // Hide Input element
              $(element).hide();

              // Update viewing value and display it
              $(element).prev('.edit').show();
              $(element).prev('.edit').text(value);
            }
          });
        });
      });
      </script>

   </body>
</html>

6. Demo

View Demo


7. Conclusion

You can also use ContentEditable attribute in a container to add inline data edit.

Read the values and send AJAX request to update the record.

If you found this tutorial helpful then don't forget to share.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

1 thought on “Live Editable table with jQuery AJAX in CodeIgniter 3”

Leave a Comment