Codeigniter 4 CRUD with Datatables Example

Crud with datatables in CodeIgniter 4; In this tutorial, i will show you how create to create crud (create, read, update and delete) application in codeigniter 4 app with bootstrap and mysql using datatable.

Generically, DataTables is a jQuery (Javascript library) based table advancement plug-in, It brings coherence in the data analysis process, ideally offers adding, sorting, paging and filtering abilities to plain HTML tables with minimal effort.

You can very easily display the data in Codeigniter 4 app by using jQuery Datatable. For example, there are thousand of records in your database table but you want to display only 10 or 15 rows on one page. So you can easily do this work with the help of Jquery DataTable.

All you need to do is include jQuery DataTable in your codeigniter 4 app. and can use its functions like adding, sorting, paging and filtering abilities to plain HTML tables with minimal effort.

In this Codeigniter 4 crud using datatable with bootstrap and mysql database example tutorial, I will cover the following topics:

Installing Codeigniter with the composer package.
Creating views to handle CRUD operations.
Integrating Bootstrap 4 in Codeigniter.
Integrating jQuery in Codeigniter.
Integrating DataTables in Codeigniter.
Create(insert) data into MySQL DB.
Read(Fetch)data from MySQL DB.
Update(Modify) data from MySQL DB.
Delete data from the MySQL DB.

Codeigniter 4 CRUD with Datatables

Install Codeigniter 4 Application
Basic App Configurations
Create Database and Table
Setup Database Credentials
Create Model Class
Create Controller Class
Create Views
Setup Routes
Start Development server

Step 1 – Install Codeigniter 4 Application

First of all, you need to ownload the latest version of Codeigniter 4. So, visit this link https://codeigniter.com/download Download Codeigniter 4 app and unzip the setup in your local system xampp/htdocs/ .

Note that, please change the download folder name “demo”.

Step 2 – Basic App Configurations

Now, you need to some basic configuration on the app/config/app.php file, so let’s go to application/config/config.php and open this file on text editor.

Set Base URL like this

public $baseURL = 'http://localhost:8080';
To
public $baseURL = 'http://localhost/demo/';

Step 3 – Create Database and Table

Create a database and table by executing the following SQL query:

CREATE DATABASE demo;

CREATE TABLE users (
    id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
    name varchar(100) NOT NULL COMMENT 'Name',
    email varchar(255) NOT NULL COMMENT 'Email Address',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='demo database' AUTO_INCREMENT=1;

INSERT INTO `users` (`id`, `name`, `email`) VALUES
(1, 'Paul Bettany', '[email protected]'),
(2, 'Vanya', '[email protected]'),
(3, 'Luther', '[email protected]'),
(4, 'John Doe', '[email protected]'),
(5, 'Paul Bettany', '[email protected]'),
(6, 'Vanya', '[email protected]'),
(7, 'Luther', '[email protected]'),
(8, 'Wayne Barrett', '[email protected]'),
(9, 'Vincent Ramos', '[email protected]'),
(10, 'Susan Warren', '[email protected]'),
(11, 'Jason Evans', '[email protected]'),
(12, 'Madison Simpson', '[email protected]'),
(13, 'Marvin Ortiz', '[email protected]'),
(14, 'Felecia Phillips', '[email protected]'),
(15, 'Tommy Hernandez', '[email protected]');

Step 4 – Setup Database Credentials

To connect your codeigniter 4 app to the database. So, visit app/Config/ directory and open Database.php. Then add the databasae details like below into database.php file:

public $default = [
        'DSN'      => '',
        'hostname' => 'localhost',
        'username' => 'test',
        'password' => '4Mu99BhzK8dr4vF1',
        'database' => 'demo',
        'DBDriver' => 'MySQLi',
        'DBPrefix' => '',
        'pConnect' => false,
        'DBDebug'  => (ENVIRONMENT !== 'development'),
        'cacheOn'  => false,
        'cacheDir' => '',
        'charset'  => 'utf8',
        'DBCollat' => 'utf8_general_ci',
        'swapPre'  => '',
        'encrypt'  => false,
        'compress' => false,
        'strictOn' => false,
        'failover' => [],
        'port'     => 3306,
	];

Step 5 – Create Model Class

Create UserModel.php file. So, visit app/Models/ directory and create UserModel.php.Then add the following code into it:

<?php 
namespace App\Models;
use CodeIgniter\Model;

class UserModel extends Model
{
    protected $table = 'users';
    protected $primaryKey = 'id';
    protected $allowedFields = ['name', 'email'];
}

Step 6 – Create Controller Class

Create UserCrudController.php file. So, visit app/Controllers directory and create UserCrudController.php.Then add the following code into it:

<?php 
namespace App\Controllers;
use App\Models\UserModel;
use CodeIgniter\Controller;

class UserCrudController extends Controller
{
    // show users list
    public function index(){
        $userModel = new UserModel();
        $data['users'] = $userModel->orderBy('id', 'DESC')->findAll();
        return view('list', $data);
    }


    // add user form
    public function create(){
        return view('add_user');
    }
 
    // insert data
    public function store() {
        $userModel = new UserModel();
        $data = [
            'name' => $this->request->getVar('name'),
            'email'  => $this->request->getVar('email'),
        ];
        $userModel->insert($data);
        return $this->response->redirect(site_url('/list'));
    }

    // show single user
    public function singleUser($id = null){
        $userModel = new UserModel();
        $data['user_obj'] = $userModel->where('id', $id)->first();
        return view('edit_user', $data);
    }

    // update user data
    public function update(){
        $userModel = new UserModel();
        $id = $this->request->getVar('id');
        $data = [
            'name' => $this->request->getVar('name'),
            'email'  => $this->request->getVar('email'),
        ];
        $userModel->update($id, $data);
        return $this->response->redirect(site_url('/list'));
    }
 
    // delete user
    public function delete($id = null){
        $userModel = new UserModel();
        $data['user'] = $userModel->where('id', $id)->delete($id);
        return $this->response->redirect(site_url('/list'));
    }    
}

UserCrudController crud controller’s methods will work as follows:

The index() function renders the users list template into the view.
The create() function renders the add user form into the view.
The store() function store data into database.
The singleUser() function retrive data from database and send it to edit form.
The update() function update data into database.
The delete() function permanently delete data into database.

Step 7 – Create Views

In this step, visit app/Views/ and create some views files for display add, edit form and usres list.

Create list.php view file, so visit app/views/ directory and create list.php file. Then add the following HTML into list.php file:

<!doctype html>
<html lang="en">
  <head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <title>Codeigniter 4 CRUD App Example - laratutorials.com</title>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">

</head>
<body>

<div class="container mt-4">
    <div class="d-flex justify-content-end">
        <a href="<?php echo site_url('/user-form') ?>" class="btn btn-success mb-2">Add User</a>
    </div>
    <?php
     if(isset($_SESSION['msg'])){
        echo $_SESSION['msg'];
      }
     ?>
  <div class="mt-3">
     <table class="table table-bordered" id="users-list">
       <thead>
          <tr>
             <th>User Id</th>
             <th>Name</th>
             <th>Email</th>
             <th>Action</th>
          </tr>
       </thead>
       <tbody>
          <?php if($users): ?>
          <?php foreach($users as $user): ?>
          <tr>
             <td><?php echo $user['id']; ?></td>
             <td><?php echo $user['name']; ?></td>
             <td><?php echo $user['email']; ?></td>
             <td>
              <a href="<?php echo base_url('edit-view/'.$user['id']);?>" class="btn btn-primary btn-sm">Edit</a>
              <a href="<?php echo base_url('delete/'.$user['id']);?>" class="btn btn-danger btn-sm">Delete</a>
              </td>
          </tr>
         <?php endforeach; ?>
         <?php endif; ?>
       </tbody>
     </table>
  </div>
</div>
 

<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css">
<script type="text/javascript" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script>
    $(document).ready( function () {
      $('#users-list').DataTable();
  } );
</script>
</body>
</html>

And the list.php file will display data in Codeigniter 4 app.

Next, Create add_user.php view file, so visit application/views/ directory and create add_user.php file. Then add the following HTML into add_user.php file:

<!DOCTYPE html>
<html>

<head>
  <title>Codeigniter 4 Add Form With Validation - Laratutorials.com</title>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
  <style>
    .container {
      max-width: 500px;
    }

    .error {
      display: block;
      padding-top: 5px;
      font-size: 14px;
      color: red;
    }
  </style>
</head>

<body>
  <div class="container mt-5">
    <form method="post" id="add_create" name="add_create" 
    action="<?= site_url('/submit-form') ?>">
      <div class="form-group">
        <label>Name</label>
        <input type="text" name="name" class="form-control">
      </div>

      <div class="form-group">
        <label>Email</label>
        <input type="text" name="email" class="form-control">
      </div>

      <div class="form-group">
        <button type="submit" class="btn btn-primary btn-block">Update Data</button>
      </div>
    </form>
  </div>

  <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.2/jquery.validate.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.2/additional-methods.min.js"></script>
  <script>
    if ($("#add_create").length > 0) {
      $("#add_create").validate({
        rules: {
          name: {
            required: true,
          },
          email: {
            required: true,
            maxlength: 60,
            email: true,
          },
        },
        messages: {
          name: {
            required: "Name is required.",
          },
          email: {
            required: "Email is required.",
            email: "It does not seem to be a valid email.",
            maxlength: "The email should be or equal to 60 chars.",
          },
        },
      })
    }
  </script>
</body>

</html>

And the add_user.php form will send data into the the ci controller method.

Create edit_view.php view file, so visit application/views/ directory and create edit_view.php file. Then add the following HTML into edit_view.php file:

<!DOCTYPE html>
<html>

<head>
  <title>Codeigniter 4 Edit Form Example - Laratutorials.com</title>
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
  <style>
    .container {
      max-width: 500px;
    }

    .error {
      display: block;
      padding-top: 5px;
      font-size: 14px;
      color: red;
    }
  </style>
</head>

<body>
  <div class="container mt-5">
    <form method="post" id="update_user" name="update_user" 
    action="<?= site_url('/update') ?>">
      <input type="hidden" name="id" id="id" value="<?php echo $user_obj['id']; ?>">

      <div class="form-group">
        <label>Name</label>
        <input type="text" name="name" class="form-control" value="<?php echo $user_obj['name']; ?>">
      </div>

      <div class="form-group">
        <label>Email</label>
        <input type="email" name="email" class="form-control" value="<?php echo $user_obj['email']; ?>">
      </div>

      <div class="form-group">
        <button type="submit" class="btn btn-danger btn-block">Save Data</button>
      </div>
    </form>
  </div>

  <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.2/jquery.validate.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.2/additional-methods.min.js"></script>
  <script>
    if ($("#update_user").length > 0) {
      $("#update_user").validate({
        rules: {
          name: {
            required: true,
          },
          email: {
            required: true,
            maxlength: 60,
            email: true,
          },
        },
        messages: {
          name: {
            required: "Name is required.",
          },
          email: {
            required: "Email is required.",
            email: "It does not seem to be a valid email.",
            maxlength: "The email should be or equal to 60 chars.",
          },
        },
      })
    }
  </script>
</body>

</html>

Step 8 – Setup Routes

To define a route, So, visit app/Config/ directory and open Routes.php file. Then add the following routes into it:

// CRUD RESTful Routes
$routes->setDefaultController('UserCrudController');
$routes->get('list', 'UserCrudController::index');
$routes->get('user-form', 'UserCrudController::create');
$routes->post('submit-form', 'UserCrudController::store');
$routes->get('edit-view/(:num)', 'UserCrudController::singleUser/$1');
$routes->post('update', 'UserCrudController::update');
$routes->get('delete/(:num)', 'UserCrudController::delete/$1');

Note that, the routes will be create, read, update and delete data on view.

Step 9 – Start Development server

Execute the following command into command prompt or terminal to start the codeigniter 4 application:

php spark serve

Then visit your web browser and hit the following url on it:

http://localhost/demo/

OR

http://localhost:8080/

Conclusion

Codeigniter 4 crud using datatable withbootstrap and mysql example;In this tutorial, you have learned successfully how implement crud operation in Codeigniter 4 apps using datatable plugin with bootstrap and mysql database.