Codeigniter 4 DataTables - How to Use Server-side DataTables in CodeIgniter 4

             DataTables is a table-enhancing plug-in for the jQuery Javascript library that helps in adding sorting, paging, and filtering abilities to plain HTML tables with minimal effort. The main goal is to enhance the accessibility of data in normal HTML tables.

Table Of Content

1 Prerequisites

1.) PHP version of >=8.1
2.) Mysql

2 Introduction

This tutorial will cover DataTables Server-side Processing in CodeIgniter 4. You will learn how to use server-side DataTables in CodeIgniter to fetch data from a MySQL database through Ajax. Server-side processing ensures that an Ajax request is made for every table redraw, returning only the necessary data. This is particularly useful for large datasets, as demonstrated in this DataTables server side processing in CodeIgniter 4 example.

Processing modes:

• Client-side processing : The full data set is loaded upfront, and data processing is done in the browser.
• Server-side processing : An Ajax request is made for every table redraw with only the required data returned. The data processing is performed on the server. This is useful when working with large datasets.

3 Create / Install a Codeigniter 4 Project

3.1 Install Codeigniter 4 Project

First, make sure your computer has a composer.
Use the following command to install new Codeigniter Project.

composer create-project codeigniter4/appstarter ci-4-datatable-app

Then, navigate to your project directory:

cd ci-4-datatable-app

3.2 Configure Environment and MySql Database

Rename the env file to .env and set the development mode in the .env file also configure mysql:

# CI_ENVIRONMENT = production
CI_ENVIRONMENT = development

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=ci4_datatable
DB_USERNAME=root
DB_PASSWORD=

4 Install DataTables

Download DataTables by either directly downloading the files or using a package manager like npm or CDN.

Using CDN:

In your view file, add the following lines to include the DataTables library:

<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.11.4/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap5.min.js"></script>

5 Create New Controller: UserController

Create a controller to handle data requests using the following command:

php spark make:controller UserController

In app/Controllers/UserController.php:

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

class UserController extends BaseController
{
    public function index()
    {
        return view('users');
    }

    public function getUsers()
{
    $model = new UserModel();
    $request = \Config\Services::request();

     $limit = $request->getPost('length');
    $start = $request->getPost('start');
    $order_column_value = $request->getPost('order')[0]['column'];
    $order=$request->getPost('columns')[$order_column_value]['data'];
    $dir = $request->getPost('order')[0]['dir'];

    $totalData = $model->countAll();
    $totalFiltered = $totalData;

    if(empty($request->getPost('search')['value']))
    {
      $users = $model->orderBy($order, $dir)->findAll($limit, $start);
    }
    else {
        $search = $request->getPost('search')['value'];
        $users = $model->like('name', $search)->orLike('email', $search)->orderBy($order, $dir)->findAll($limit, $start);
        $totalFiltered = $model->like('name', $search)->orLike('email', $search)->countAllResults();
    }
    $data = array();
    if(!empty($users))
    {
        foreach ($users as $user)
        {
            $nestedData['id'] = $user['id'];
            $nestedData['name'] = $user['name'];
            $nestedData['email'] = $user['email'];
            $nestedData['created_at'] = $user['created_at'];
            $data[] = $nestedData;
        }
    }

    $json_data = array(
        "draw" => intval($request->getPost('draw')),
        "recordsTotal" => intval($totalData),
        "recordsFiltered" => intval($totalFiltered),
        "data" => $data
    );

    return $this->response->setJSON($json_data);
}

}
?>

6 Create Migration and Model

Create a migration for the users table and a model to store data:

php spark make:model UserModel

Configure the UserModel:

<?php
namespace App\Models;

use CodeIgniter\Model;

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

Create a migration file to define the table structure:

php spark make:migration AddUser

Edit the migration file to define the table structure:

<?php
namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class AddUser extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type' => 'BIGINT',
                'constraint' => 255,
                'unsigned' => true,
                'auto_increment' => true
            ],
            'name' => [
                'type' => 'VARCHAR',
                'constraint' => '255',
            ],
            'email' => [
                'type' => 'longtext'
            ],
            'created_at' => [
                'type' => 'TIMESTAMP',
                'null' => true
            ],
            'updated_at' => [
                'type' => 'TIMESTAMP',
                'null' => true
            ],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->createTable('users');
    }

    public function down()
    {
        $this->forge->dropTable('users');
    }
}

Run the migration:

php spark migrate

7 Create Database Seeder

For seeding the test data, create a seeder class:

php spark make:seeder UserSeeder

In app/Database/Seeds/UserSeeder.php:

<?php
namespace App\Database\Seeds;

use CodeIgniter\Database\Seeder;
use CodeIgniter\I18n\Time;
use App\Models\UserModel;
class UserSeeder extends Seeder
{
    public function run()
    {
        $user = new UserModel;
        $faker = \Faker\Factory::create();

        for ($i = 0; $i < 100; $i++) {
          $user->save(
                [
                    'name'        =>    $faker->name,
                    'email'       =>    $faker->email,
                    'created_at'  =>    Time::createFromTimestamp($faker->unixTime()),
                    'updated_at'  =>    Time::now()
                ]
            );
        }
    }
}

Run the seeder:

php spark db:seed UserSeeder

8 Create a View

Create a view file users.php in the app/Views directory to display the DataTable:

  <!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>DataTables with CodeIgniter 4</title>
     <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/1.11.4/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.22/js/dataTables.bootstrap5.min.js"></script>
</head>
<body>
    <div class="container">
        <h1>User List</h1>
        <table id="userTable" class="table table-bordered ">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Created At</th>
                </tr>
            </thead>
            <tbody>
                <!-- Data will be loaded here -->
            </tbody>
        </table>
    </div>

    <script>
        $(document).ready(function() {
            $('#userTable').DataTable({
                "processing": true,
    "serverSide": true,
                "ajax": {
                    "url": "<?php echo base_url('usercontroller/getusers'); ?>",
                    "type": "POST"
                },
                "columns": [
                    { data: 'id' },
                    { data: 'name' },
                    { data: 'email' },
                    { data: 'created_at' }
                ]
            });
        });
    </script>
</body>
</html>

9 Define a Route

In app/Config/Routes.php, define routes for the controller:

use CodeIgniter\Router\RouteCollection;
/**
 * @var RouteCollection $routes
 */
$routes->get('/', 'Home::index');
$routes->get('users', 'UserController::index');
$routes->post('usercontroller/getusers', 'UserController::getUsers');

10 Folder Structure

Ensure the folder structure is correctly set up with controllers, views, and routes to maintain the app's organization.

11 Run Web Server to Test the App

Finally, run your development server to test server-side DataTables in CodeIgniter 4 functionality:

php spark serve

Visit http://localhost:8080/users

12 Conclusion

This setup provides you with a fully functional Datatables server side processing in CodeIgniter 4 example. You can customize it as needed for your application. The guide covers how to use server-side DataTables in CodeIgniter, utilizing DataTables server side processing in CodeIgniter 4 jQuery integration.

Tags