How to Implement jQuery Datatable in CodeIgniter 4

jQuery provides the datatable library to use in an HTML table. The jQuery datatable provides you the flexibility of pagination, searching, and ordering the data in the table. Even you can apply custom filters to the data. It also allows us to use column filters inside the table. You can implement it on any server-side or client-side language that contains an HTML element. Today, you will learn the implementation of jQuery datatable in CodeIgniter 4. It is very easy to implement it. Lastly, I have shown you the use of seeder and faker factory in CodeIgniter 4. By using of seeder and faker factory. I had inserted the sample data in a table. Hence, for implementing the jQuery datatable in CodeIgniter 4, I will be using the same approach. I am not gonna insert records manually in the table. So, let’s do that quickly.

Prerequisites

Before applying the datatable, you need to have a CodeIgniter 4 project. Here, I am assuming you are ready for creating a new project. So, it will require the below configuration.

• PHP >= 7.3
• MySQL (version > 5)
• Apache/Nginx Server
• VS Code Editor
• Composer

Once, you are done let’s create the project.

Create New Project in CodeIgniter 4

You can download CodeIgniter 4 from the official website. But, I am gonna install it through composer. You need to open the terminal and hit the below command.

composer create-project codeigniter4/appstarter ci4-datatable

It will create the project folder and install CodeIgniter 4. It will take a couple of minutes to finish the installation.

In the next step, you will need to create a database. Here, I will be using the MySQL command line for database management.

How to Remove index.php From URL in CodeIgniter 4

Create and Configure Database

Firstly, I will create a database, then it will need to configure it with newly created CodeIgniter 4 project.

CREATE DATABASE ci4_datatable;

After creating the database, let’s connect it with our project. In order to configure the database, you will have to go through the below steps-

• Rename the env file to the .env file. You can make a copy of this file even then rename it.
• Next, open the .env file and change the environment to development. By default, it is production.
• Lastly, go to the database section inside this file and replace the credentials as showing below.

#--------------------------------------------------------------------
# DATABASE
#--------------------------------------------------------------------

database.default.hostname = localhost
database.default.database = ci4_form
database.default.username = root
database.default.password = root
database.default.DBDriver = MySQLi

Now, all set for the database configuration. Therefore, let’s create a migration file to implement the datatable in CodeIgniter 4.

Upload Multiple Image with Validation in Codeigniter 4

Create a Migration File in CodeIgniter 4

Here, you need to create a migration file for the users table. Actually, you need the data in the form of a table. On that data, we can apply the datatable in CodeIgniter 4.

php spark make:migration users --table

After hitting the above command, you will have a migration file. The migration file will be inside the app/Database/Migrations folder. Here, you will be having a file with the name something timestamp_Users.php. Navigate to this file and paste the below schema.

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class Users extends Migration
{
    public function up()
    {
        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'auto_increment' => true
            ],
            'name' => [
                'type' => 'VARCHAR',
                'constraint' => '30'
            ],
            'email' => [
                'type' => 'VARCHAR',
                'constraint' => '50'
            ],
            'password' => [
                'type' => 'VARCHAR',
                'constraint' => '20'
            ],
            'phone' => [
                'type' => 'VARCHAR',
                'constraint' => '15'
            ],
            'address' => [
                'type' => 'TEXT'
            ],
            'created_at datetime default current_timestamp',
            'updated_at datetime default current_timestamp on update current_timestamp'
        ]);
        $this->forge->addKey('id', true);
        $this->forge->createTable('users');
    }

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

Here, we defined the schema for the users table. Hence, it will generate the fields inside the user table. So, let’s migrate the above schema to the database.

How to Upload Image and File in Codeigniter 4 with Validation

Migrate Tables in CodeIgniter 4

For migrating the tables, you need the migration file and the schema for the tables. The below command will migrate the table with its schema inside the database.

php spark migrate

After the successful migration, you may checkout the database. Here, the users table schema has been dumped out.

Now, you can create a model to configure with the table.

Form Validation Example in Codeigniter 4 For Beginners

Create a Model in CodeIgniter 4

While implementing the datatable in CodeIgniter 4, I want to connect with a dynamic data. Hence, it will require a model to connect with the table. Therefore, let’s create it quickly.

php spark make:model User

The above command will create a model file inside the app/Models folder. Now, you need to define some properties inside it. So, just update it as showing below. Here, I have defined the table name, primary key, allowed fields attributes in the form of an array.

<?php

namespace App\Models;

use CodeIgniter\Model;

class User extends Model
{
    protected $DBGroup              = 'default';
    protected $table                = 'users';
    protected $primaryKey           = 'id';
    protected $useAutoIncrement     = true;
    protected $protectFields        = true;
    protected $allowedFields        = ['name', 'email', 'password', 'phone', 'address'];

    // Dates
    protected $useTimestamps        = false;
    protected $dateFormat           = 'datetime';
    protected $createdField         = 'created_at';
    protected $updatedField         = 'updated_at';
}

Here, the model configuration is completed. Now, let’s create a seeder file to seed the fake data to the users table.

Create a CRUD Application in CodeIgniter 4 For Beginners

Create a Seeder For Seeding Sample Data

The CodeIgniter 4 provides a seeder class. It is used to dump the rows inside the table of a database. Here, we can use to seed some fake data inside the users table. After that we’ll apply the datatable on it.

php spark make:seeder UserSeeder

After creating the UserSeeder file, the default code will look like this.

There is a function inside the UserSeeder class that is run(). Here, you will need to use the faker factory class to generate fake data. So, just add the below snippet there.

<?php

namespace App\Database\Seeds;

use CodeIgniter\Database\Seeder;
use CodeIgniter\I18n\Time;
use App\Models\User;

class UserSeeder extends Seeder
{
    public function run()
    {

        $user = new User;
        $faker = \Faker\Factory::create();

        for ($i = 0; $i < 220; $i++) {
          $user->save(
                [
                    'name'        =>    $faker->name,
                    'email'       =>    $faker->email,
                    'password'    =>    password_hash($faker->password, PASSWORD_DEFAULT),
                    'phone'       =>    $faker->phoneNumber,
                    'address'     =>    $faker->address,
                    'created_at'  =>    Time::createFromTimestamp($faker->unixTime()),
                    'updated_at'  =>    Time::now()
                ]
            );
        }
    }
}

CRUD Application in PHP 8 Using Prepared Statement

Run Seeder File in CodeIgniter 4

Here, you have defined the faker factory attributes in the seeder file. Now, it’s time to run it so that it can generate the specified number of rows inside the users table. Hence, hit the below command.

php spark db:seed UserSeeder

It seeded the fake data in the users table.

Here, I have generated 200+ rows using seeder. Let’s checkout the table for the records.

Now, we have the data to apply the datatable in CodeIgniter 4. Hence, let’s create a controller to write the functionality.

What is SQL Injection and How to Prevent it in PHP

Create a Controller in CodeIgniter 4

For creating a controller, I will use the same approach that we have used earlier. Yes, you have assumed right. I will use spark command. So, let’s create the controller.

php spark make:controller UserController

The above command will create a controller named UserController.php. Here, you need to write the function to fetch data from the users table using User model. So, just paste the below code and move to the next step.

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\User;

class UserController extends BaseController
{
    private $user;
    /**
     * constructor
     */
    public function __construct()
    {
        $this->user = new User();
    }


    /**
     * create function to fetch users data
     * @return array on view
     */
    public function users() {
        $users = $this->user->findAll();
        return view('users', compact('users'));
    }
}

• Firstly, I have instantiated the User model inside the constructor.
• Then inside the users() function, I have fetched all the rows from the users table. This is database modeling. Hence, no need to write the raw SQL query here.
• It will fetch all the rows and return them to the users view.

Check Email Available in PHP MySQL Using jQuery and Ajax

Create a View For Datatable in CodeIgniter 4

To display the data, you will need the view. Hence, we will create a view with the name users.php inside the app/Views folder.

After creating the view, let’s put the HTML code to make a table using Bootstrap 5.

<!doctype html>
<html lang="en">
  <head>
    <title>Datatable in CodeIgniter 4</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

     <!-- bootstrap 5 CSS -->
     <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-eOJMYsd53ii+scO/bJGFsiCZc+5NDVN2yr8+0RDqr0Ql0h+rP48ckxlpbzKgwra6" crossorigin="anonymous">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.4.1/font/bootstrap-icons.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css"/>
  </head>
  <body>
  <div class="container-fluid py-4">
    <h4 class="text-center"> Datatable in CodeIgniter 4 </h3>
    <div class="row mt-4">
        <div class="col-xl-12">
            <div class="card">
                <div class="card-header bg-primary text-white">
                    <h5 class="card-title">Users </h4>
                </div>

                <div class="card-body">
                    <table class="table table-striped table-bordered" id="userTable">
                        <thead>
                            <tr>
                                <th>Id </th>
                                <th>Name </th>
                                <th>Email</th>
                                <th>Phone no. </th>
                                <th>Address </th>
                            </tr>
                        </thead>

                        <tbody>
                        <?php 
                            if (count($users) > 0):
                                foreach ($users as $user): ?>
                                    <tr>
                                        <td><?= $user['id'] ?></td>
                                        <td><?= $user['name'] ?></td>
                                        <td><?= $user['email'] ?></td>
                                        <td><?= $user['phone'] ?></td>
                                        <td><?= $user['address'] ?></td>
                                    </tr>
                                <?php endforeach;
                            else: ?>
                                <tr rowspan="1">
                                    <td colspan="4">
                                        <h6 class="text-danger text-center">No usres found</h6>
                                    </td>
                                </tr>
                            <?php endif ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
  </div>
   
    <script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
    <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>
    <script>
        $(document).ready(function() {
            $("#userTable").dataTable();
        });
    </script>
  </body>
</html>

In the above snippet, I have iterated the data coming from the controller. Also, at the bottom side, I have applied the datatable on the id attribute of the HTML table.

Now, you need a route to check the result.

How to Implement jQuery Datatable in PHP with MySQL

Create Route

For creating a route, let’s open the Routes.php file and the below route.

$routes->get('users', 'UserController::users');

Now, you are good to check the result. Let’s run the application using the below command-

php spark serve

Check Result of Datatable in CodeIgniter 4

To check the result, you need to open the browser. Now, hit the route and see the below result. Here, the datatable has been applied to the view.

You will have the pagination, soring, searching, and count of the data in this table.

Let’s try to order the row or search any record from the search box. You can see the result of filtered data.

Conclusion

We applied jQuery datatable in CodeIgniter 4 on the sample data. For generating sample data, we used seeder and faker factory class. After fetching all the rows from the databse, we returned it to the view. In the view, we applied the datatable on the HTML table. On the result, we have the pagination, searching, sorting and many more feature to use.