Live Editable table with jQuery AJAX in CodeIgniter 3

Last updated on by

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

Demo Download

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.

© 2016-2024 Makitweb, All rights reserved