Laravel 9 Ajax Datatables Filter

Laravel 9 Ajax Datatables Filter

Download Laravel App

composer create-project --prefer-dist laravel/laravel my-app

C:\xampp\htdocs\laravel>composer create-project --prefer-dist laravel/laravel my-app

Connecting our Database

open .env file root directory.

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=laraveldb

DB_USERNAME=root

DB_PASSWORD=

Create Controller

php artisan make:controller StudentController

C:\xampp\htdocs\laravel\my-app>php artisan make:controller StudentController

app\Http\Controllers\StudentController.php

//app\Http\Controllers\StudentController.php
<?php
 
namespace App\Http\Controllers;
 
use Illuminate\Http\Request;
use App\Models\Student;
 
class StudentController extends Controller
{
    public function index()
    {
        $standards = Student::select('standard')
            ->groupBy('standard')
            ->get();
 
        $results = Student::select('result')
            ->groupBy('result')
            ->get();
 
        return view('students', compact('standards', 'results'));
    }
 
    public function getStandard(Request $request)
    {
        if ($request->ajax()) {
            $standards = Student::select('standard')
                ->groupBy('standard')
                ->get();
 
            return response()->json($standards);
        }
    }
 
    public function getResult(Request $request)
    {
        if ($request->ajax()) {
 
            $results = Student::select('result')
                ->groupBy('result')
                ->get();
 
            return response()->json($results);
        }
    }
 
    public function records(Request $request)
    {
        if ($request->ajax()) {
 
            if (request('std') && request('res')) {
                $students = Student::where('standard', '=', request('std'))->where('result', '=', request('res'))
                    ->latest()
                    ->get();
            } else {
                $students = Student::when(request('std'), function ($query) {
                    $query->where('standard', '=', request('std'));
                })
                    ->when(request('res'), function ($query) {
                        $query->where('result', '=', request('res'));
                    })
                    ->latest()
                    ->get();
            }
 
            return response()->json([
                'students' => $students
            ]);
        } else {
            abort(403);
        }
    }
}

Database Migration
php artisan make:model Student -m
C:\xampp\htdocs\laravel\my-app>php artisan make:model Student -m
database/migrations/create_students_table.php

//atabase/migrations/create_students_table.php
<?php
 
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
 
return new class extends Migration
{
    public function up()
    {
        Schema::create('students', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->integer('standard');
            $table->integer('percentage');
            $table->string('result');
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::dropIfExists('students');
    }
};

run this migration
C:\xampp\htdocs\laravel\my-app>php artisan migrate

database table data

CREATE TABLE `students` (

`id` bigint(20) UNSIGNED NOT NULL,

`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

`standard` int(11) NOT NULL,

`percentage` int(11) NOT NULL,

`result` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

`created_at` timestamp NULL DEFAULT NULL,

`updated_at` timestamp NULL DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table `students`

INSERT INTO `students` (`id`, `name`, `standard`, `percentage`, `result`, `created_at`, `updated_at`) VALUES

(1, 'Cairocoders Ednalan', 5, 75, '75', '2022-12-22 08:38:25', '2022-12-22 08:38:25'),

(2, 'Charde Marshall', 8, 75, '75', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(3, 'Airi Satou', 7, 85, '85', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(4, 'Angelica Ramos', 5, 95, '95', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(5, 'Ashton Cox', 6, 95, '95', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(6, 'Bradley Greer', 7, 80, '80', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(7, 'Brenden Wagner', 8, 80, '82', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(8, 'Brielle Williamson', 9, 85, '85', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(9, 'Bruno Nash', 10, 80, '86', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(10, 'Caesar Vance', 7, 90, '90', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(11, 'Cara Stevens', 8, 90, '91', '2022-12-22 00:38:25', '2022-12-22 00:38:25'),

(12, 'Cedric Kelly', 2, 90, '92', '2022-12-22 00:38:25', '2022-12-22 00:38:25');

View Blade
Bootstrap 5
https://getbootstrap.com/docs/5.0/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css

Jquery
https://jquery.com/download/
CDN : jsDelivr CDN
https://www.jsdelivr.com/package/npm/jquery
https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js

https://datatables.net/
DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, built upon the foundations of progressive enhancement, that adds all of these advanced features to any HTML table.
resources/views/students.blade.php

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

//resources/views/students.blade.php
<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Laravel</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.1/css/jquery.dataTables.min.css" />
</head>
<body>
    <div class="container">
        <div class="row">
            <h1 class="text-center">Laravel 9 Ajax Datatables Filter</h1>
            <hr>
        </div>
        <div class="row">
            <div class="col-12">
                <div class="row">
                    <div class="col-6">
                        <div class="input-group mb-3">
                            <div class="input-group-prepend">
                                <label class="input-group-text" for="inputGroupSelect01">Standard</label>
                            </div>
                            <select class="custom-select form-select" id="select_std">
                                <option value="">Choose...</option>
                            </select>
                        </div>
                    </div>
                    <div class="col-6">
                        <div class="input-group mb-3">
                            <div class="input-group-prepend">
                                <label class="input-group-text" for="inputGroupSelect01">Result</label>
                            </div>
                            <select class="custom-select form-select" id="select_res">
                                <option value="">Choose...</option>
                            </select>
                        </div>
                    </div>
                </div>
                <div>
                    <button id="filter" class="btn btn-sm btn-outline-success">Filter</button>
                    <button id="reset_std" class="btn btn-sm btn-outline-success">Reset Standard</button>
                    <button id="reset_res" class="btn btn-sm btn-outline-success">Reset Result</button>
                    <button id="reset" class="btn btn-sm btn-outline-warning">Reset</button>
                </div>
            </div>
        </div>
        <div class="row">
                    <div class="col-12">
                        <div class="table-responsive">
                            <table class="display" id="record_table" style="width:100%">
                                <thead>
                                    <tr>
                                        <th>ID</th>
                                        <th>Name</th>
                                        <th>Standard</th>
                                        <th>Percentage</th>
                                        <th>Result</th>
                                        <th>Date</th>
                                    </tr>
                                </thead>
                            </table>
                        </div>
                    </div>
        </div>
</div>
<script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/moment.min.js"></script>
<script>
        function fetch_std() {
            $.ajax({
                url: "{{ route('standards') }}",
                type: "GET",
                dataType: "json",
                success: function(data) {
                    var stdBody = "";
                    for (var key in data) {
                        stdBody +=
                            `<option value="${data[key]['standard']}">${data[key]['standard']}</option>`;
                    }
                    $("#select_std").append(stdBody);
                }
            });
        }
        fetch_std();
        // Fetch Result
        function fetch_res() {
            $.ajax({
                url: "{{ route('results') }}",
                type: "GET",
                dataType: "json",
                success: function(data) {
                    var resBody = "";
                    for (var key in data) {
                        resBody += `<option value="${data[key]['result']}">${data[key]['result']}</option>`;
                    }
                    $("#select_res").append(resBody);
                }
            });
        }
        fetch_res();
        // Fetch Records
        function fetch(std, res) {
            $.ajax({
                url: "{{ route('students/records') }}",
                type: "GEt",
                data: {
                    std: std,
                    res: res
                },
                dataType: "json",
                success: function(data) {
                    var i = 1;
                    $('#record_table').DataTable({
                        "data": data.students,
                        "responsive": true,
                        "columns": [{
                                "data": "id",
                                "render": function(data, type, row, meta) {
                                    return i++;
                                }
                            },
                            {
                                "data": "name"
                            },
                            {
                                "data": "standard",
                                "render": function(data, type, row, meta) {
                                    return `${row.standard}th Standard`;
                                }
                            },
                            {
                                "data": "percentage",
                                "render": function(data, type, row, meta) {
                                    return `${row.percentage}%`;
                                }
                            },
                            {
                                "data": "result"
                            },
                            {
                                "data": "created_at",
                                "render": function(data, type, row, meta) {
                                    return moment(row.created_at).format('DD-MM-YYYY');
                                }
                            }
                        ]
                    });
                }
            });
        }
        fetch();
        // Filter
        $(document).on("click", "#filter", function(e) {
            e.preventDefault();
            var std = $("#select_std").val();
            var res = $("#select_res").val();
            if (std !== "" && res !== "") {
                $('#record_table').DataTable().destroy();
                fetch(std, res);
            } else if (std !== "" && res == "") {
                $('#record_table').DataTable().destroy();
                fetch(std, '');
            } else if (std == "" && res !== "") {
                $('#record_table').DataTable().destroy();
                fetch('', res);
            } else {
                $('#record_table').DataTable().destroy();
                fetch();
            }
        });
        // Reset Standard
        $(document).on("click", "#reset_std", function(e) {
            e.preventDefault();
            $("#select_std").html(`<option value="">Choose...</option>`);
            var res = $("#select_res").val();
            if (res == "") {
                $('#record_table').DataTable().destroy();
                fetch();
                fetch_std();
            } else {
                $('#record_table').DataTable().destroy();
                fetch('', res);
                fetch_std();
            }
        });
        // Reset Result
        $(document).on("click", "#reset_res", function(e) {
            e.preventDefault();
            $("#select_res").html(`<option value="">Choose...</option>`);
            var std = $("#select_std").val();
            if (std == "") {
                $('#record_table').DataTable().destroy();
                fetch();
                fetch_res();
            } else {
                $('#record_table').DataTable().destroy();
                fetch(std, '');
                fetch_res();
            }
        });
        // Reset
        $(document).on("click", "#reset", function(e) {
            e.preventDefault();
            $("#select_std").html(`<option value="">Choose...</option>`);
            $("#select_res").html(`<option value="">Choose...</option>`);
            $('#record_table').DataTable().destroy();
            fetch();
            fetch_std();
            fetch_res();
        });
    </script>
</body>
</html>

Routes
routes/web.php

//routes/web.php
<?php
 
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\ImageController;
use App\Http\Controllers\StudentController;
 
//Route::get('/', function () {
//    return view('welcome');
//});
 
Route::get('students', [StudentController::class, 'index'])->name('students');
Route::get('standards', [StudentController::class, 'getStandard'])->name('standards');
Route::get('results', [StudentController::class, 'getResult'])->name('results');
Route::get('students/records', [StudentController::class, 'records'])->name('students/records');

Run C:\xampp\htdocs\laravel\my-app>php artisan serve
Starting Laravel development server: http://127.0.0.1:8000

Tutorial101

article

Saturday, January 7, 2023

Laravel 9 Ajax Datatables Filter

Related Post

Search This Blog

HOME

Category

Popular Posts