Laravel 6 Tutorial: CRUD with Datatable

thumbnail

QUICK SUMMARY: The purpose of this tutorial is we want to learn how to create CRUD (Create, Read, Update, and Delete) with Laravel framework and DataTable. The app we will build is CRUD for employees in some companies. Laravel is used for data processing and datatable will handle how data look at the client.

This tutorial will cover basic processes on how to create CRUD on Laravel and before following this tutorial I hope you understand basic PHP and JQuery. Okay, let’s jump to the tutorial.

demo: https://desolate-falls-43028.herokuapp.com/

Install Laravel

To create a Laravel CRUD DataTable Application, the first step we need to do is install a fresh Laravel app. To do that, open the terminal and type command below.

 composer create-project --prefer-dist laravel/laravel lara6

You can change lara6 to any as you want to the project name.

After that, create a database and update your .env file

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

Download the resource

For this Laravel CRUD DataTable application, we need some javascript library to make this application looks good. All the ingredient look like bellow
DataTable : https://datatables.net/
bootstrap: https://getbootstrap.com/docs/4.3/getting-started/download/
Picaday : https://github.com/Pikaday/Pikaday

Copy all resources to the public path looks like below.

Laravel 6 DataTable

Create Table

After all resource ready let’s start to build our application. Create an employee table.

php artisan make:model Models/Employee -m

Let me explain the code above. The make model command will create a model for our app and we specific where is the model located.

The -m at the end of the command means we create migration belongs to the model.

In database/migrations/YYY_MM_DD_TIMESTAMP_create_employees_table.php edit the up method so look like bellow

public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string("name");
        $table->string("position");
        $table->string("office");
        $table->integer("age")->unsigned();
        $table->dateTime("start_date");
        $table->decimal("salary", 20, 4);
        $table->timestamps();
    });
}

After that save it and run the migration with the command bellow

php artisan migrate

After running these commands the table will be created.

Create Controller

After creating the table, next, we need to create a controller to handle the logic. Create a controller with command bellow:

php artisan make:controller DatatableController -r

Here are available options when creating a controller in Laravel
-m, –model[=MODEL] Generate a resource controller for the given model.
-r, –resource Generate a resource controller class.
-p, –parent[=PARENT] Generate a nested resource controller class.
-h, –help Display this help message
-q, –quiet Do not output any message
-V, –version Display this application version
–ansi Force ANSI output
–no-ansi Disable ANSI output
-n, –no-interaction Do not ask any interactive question
–env[=ENV] The environment the command should run under
-v|vv|vvv, –verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

Edit Route

Next, we need to modify the route for navigation in our app. Open web.php

file in routes folder and add line some code like bellow.

Route::get('/', '[email protected]');
Route::resource('datatable', "DatatableController");

The resource route will handle GET, POST, and DELETE methods for our HTTP request.

Edit Layout

After defining the route and setting the database, next we must create the base view to display the data.

Open layout view in resources/views/layout/app.blade.php

In the header tag add some code like bellow.

<!-- Styles --> 
<link href="{{ asset('css/app.css') }}" rel="stylesheet">
<link href="{{ asset('css/datatables.min.css') }}" rel="stylesheet">
@yield("style")
<script type="text/javascript">
    window.app = '{{ url('/') }}'
</script>

Before close the body tag adds some code like bellow.

<!-- Scripts -->
<script src="{{ asset('js/jquery-3.4.1.min.js') }}"></script>
<script src="{{ asset('js/bootstrap.bundle.min.js') }}"></script>
<script src="{{ asset('js/datatables.min.js') }}"></script>
<script type="text/javascript">
    $.ajaxSetup({
        headers: {
            'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
        }
    });
</script>
@yield('script')

Display Data

For displaying the data first we need to update the blade file for the base view, create a javascript file for run the datatable plugin and of course the controller for the application logic.

Blade File

After that create view file and we name it datatable.blade.php and put in resources/views/crud folder. Paste the code bellow

@extends('layouts.app')

@section('style')
    <link href="{{ asset('css/pikaday.css') }}" rel="stylesheet">
    <style type="text/css">
        .display-none {
            display: none;
        }
        .display-block {
            display: block;
        }
        .btn {
            margin: 0.1rem 0.2rem;
        }
    </style>
@endsection

@section('content')
<div class="container">
    <div class="row justify-content-center">
        <div class="col-md-12">
            <div class="card">
                <div class="card-header">
                    <div class="d-flex">
                        <div class="p-2">Dashboard</div>
                        <div class="ml-auto p-2">
                            <!-- Button trigger modal -->
                            <button type="button" id="btnNewEmployee" class="btn btn-primary btn-sm text-right">
                                New Employee
                            </button>
                        </div>
                    </div>
                </div>

                <div class="card-body">
                    <div id="alertHome" class="show display-none alert alert-dismissible fade" role="alert">
                        <span id="alertMsgHome"></span>
                        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                            <span aria-hidden="true">×</span>
                        </button>
                    </div>
                    <div class="table-responsive">
                        <!-- DataTable -->
                        <table id="datatable" class="table">
                            <thead>
                                <tr>
                                    <th>Name</th>
                                    <th>Position</th>
                                    <th>Office</th>
                                    <th>Age</th>
                                    <th>Start date</th>
                                    <th>Salary</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Modal -->
    <div class="modal fade" id="datatableModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalCenterTitle" aria-hidden="true">
        <div class="modal-dialog modal-dialog-centered" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="exampleModalLongTitle">Employee</h5>
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">×</span>
                    </button>
                </div>
                <div class="modal-body">
                    {{-- alert-success show --}}
                    <div id="alertModal" class="show alert alert-dismissible fade" role="alert">
                        <span id="alertMessage"></span>
                        <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                            <span aria-hidden="true">×</span>
                        </button>
                    </div>
                    <form id="formEmployee" method="POST" action="{{ url('/datatable') }}">
                        <input type="hidden" name="id" id="inputId">
                      <div class="form-group row">
                        <label for="inputName" class="col-md-3 col-form-label">Name</label>
                        <div class="col-md-9">
                          <input required name="name" type="text" class="form-control" id="inputName" placeholder="Name">
                        </div>
                      </div>

                      <div class="form-group row">
                        <label for="inputPosition" class="col-md-3 col-form-label">Position</label>
                        <div class="col-md-9">
                          <input required name="position" type="text" class="form-control" id="inputPosition" placeholder="Position">
                        </div>
                      </div>

                      <div class="form-group row">
                        <label for="inputOffice" class="col-md-3 col-form-label">Office</label>
                        <div class="col-md-9">
                          <input required name="office" type="text" class="form-control" id="inputOffice" placeholder="Office">
                        </div>
                      </div>

                      <div class="form-group row">
                        <label for="inputAge" class="col-md-3 col-form-label">Age</label>
                        <div class="col-md-5">
                          <input required name="age" type="number" class="form-control" id="inputAge" placeholder="Age">
                        </div>
                      </div>

                      <div class="form-group row">
                        <label for="inputStartDate" class="col-md-3 col-form-label">Start Date</label>
                        <div class="col-md-9">
                          <input required name="startDate" type="text" class="form-control datepicker" id="inputStartDate" placeholder="Start Date">
                        </div>
                      </div>

                      <div class="form-group row">
                        <label for="inputSallary" class="col-md-3 col-form-label">Sallary</label>
                        <div class="col-md-9">
                          <input required name="salary" type="number" class="form-control" id="inputSallary" placeholder="Sallary">
                        </div>
                      </div>
                    </form>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                    <button type="submit" form="formEmployee" id="submitBtn" class="btn btn-primary">Save changes</button>
                </div>
            </div>
        </div>
    </div>
</div>
@endsection

@section('script')
    <script src="{{ asset('js/moment.js') }}"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]"></script>
    <script src="{{ asset('js/pikaday.js') }}"></script>
    <script src="{{ asset('js/pikaday.jquery.js') }}"></script>
    <script src="{{ asset('js/table.js?v=').time() }}"></script>
@endsection

As you can see we extend layout from app.blade.php and include a script that only needed in datatable.blade.php file.

In the datatable.blade.php file above we create a table for initializing the DataTable and modal for creating or update the data.

Create a JavaScript file

In datatable.blade.php we already include all the script that we needed. But there is 1 script that not come from the downloaded file. That’s is table.js. This JS file will handle javascript functionality in our app. So let’s create table.js in the public/js folder.

Here is the code inside table.js file

// Edit the data
function editTable(id) {
    $("#alertModal").toggleClass("display-none");
    $("#alertModal").addClass("display-none").removeClass("alert-danger")
    $("#inputId").val(id)
    $("#datatableModal").modal({
        backdrop: 'static',
        keyboard: false
    });
    $.ajax({
        url: window.app + "/datatable/" + id,
        dataType: 'json', // data type
        type: 'GET',
        success: function (data, textStatus, jqXHR) {
            $("#inputName").val(data.row.name);
            $("#inputPosition").val(data.row.position);
            $("#inputOffice").val(data.row.office);
            $("#inputAge").val(data.row.age);
            $("#inputSallary").val(data.row.salary);
            var date = moment(data.row.start_date, "YYYY-MM-DD").format("DD-MM-YYYY")
            $("#inputStartDate").val(date);
        }
    });
}

// Delete row in datatable
function deleteRow(id) {
    var swalWithBootstrapButtons = Swal.mixin({
        customClass: {
            confirmButton: 'btn btn-success',
            cancelButton: 'btn btn-danger'
        },
        buttonsStyling: false
    })

    swalWithBootstrapButtons.fire({
        title: 'Are you sure?',
        text: "You won't be able to revert this!",
        type: 'warning',
        showCancelButton: true,
        confirmButtonText: 'Yes, delete it!',
        cancelButtonText: 'No, cancel!',
        reverseButtons: true
    }).then((result) => {
        if (result.value) {
            $.ajax({
                url: window.app + '/datatable/' + id,
                type: 'DELETE',
                dataType: 'JSON',
                success: function (data) {
                    if (data.error) {
                        $("#alertHome").toggleClass("display-none").toggleClass("alert-success");
                        $("#alertMsgHome").html(data.msg);
                        setTimeout(function () {
                            $("#alertHome").addClass("display-none").removeClass("alert-success")
                        }, 3000);
                    } else {
                        swalWithBootstrapButtons.fire(
                            'Deleted!',
                            'Your file has been deleted.',
                            'success'
                        )
                        $("#datatable").DataTable().ajax.reload();
                    }
                }
            });
        } else if (
            /* Read more about handling dismissals below */
            result.dismiss === Swal.DismissReason.cancel
        ) {
            swalWithBootstrapButtons.fire(
                'Cancelled',
                'Your imaginary file is safe :)',
                'error'
            )
        }
    })
}

$(document).ready(function () {
    // initializing Datatable
    var table = $("#datatable").DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            url: window.app + "/datatable",
            type: "GET",
        },
        "columns": [{
                data: "name",
                name: "name"
            },
            {
                data: "position",
                name: "position"
            },
            {
                data: "office",
                name: "office"
            },
            {
                data: "age",
                name: "age"
            },
            {
                data: "start_date",
                name: "start_date"
            },
            {
                data: "salary",
                name: "salary"
            },
            {
                data: "action",
                name: "action",
                orderable: false
            },
        ]
    });

    // Display modal for add new data
    $("#btnNewEmployee").click(function (e) {
        e.preventDefault();
        $("#alertModal").addClass("display-none").removeClass("alert-danger")
        $("#inputId").val(null)
        $("#datatableModal").modal({
            backdrop: 'static',
            keyboard: false
        });
    });

    // Pikaday
    var datepicker = $('.datepicker').pikaday({
        firstDay: 1,
        format: 'D-MM-YYYY',
    });
    
    // When submit the form
    $('#formEmployee').submit(function (e) {
        e.preventDefault()
        var form = $(this),
            url = form.attr("action")
        var formData = form.serialize();
        $.ajax({
            url: window.app + "/datatable",
            data: formData,
            dataType: 'json', // data type
            type: 'POST',
            success: function (data, textStatus, jqXHR) {
                if (data.error) {
                    $("#alertModal").toggleClass("display-none").toggleClass("alert-danger");
                    $("#alertMessage").html(data.msg);
                    setTimeout(function () {
                        $("#alertModal").addClass("display-none").removeClass("alert-danger")
                    }, 3000);
                } else {
                    $("#alertHome").toggleClass("display-none").toggleClass("alert-success");
                    setTimeout(function () {
                        $("#alertHome").addClass("display-none").removeClass("alert-success")
                    }, 3000);
                    $("#alertMsgHome").html(data.msg);
                    $("#datatableModal").modal("hide");
                    $("#datatable").DataTable().ajax.reload();
                }
            }
        });
    });
  
    // Reset form when close
    $('#datatableModal').on('hidden.bs.modal', function () {
        $('#datatableModal form')[0].reset();
    });

    function deleteItem(id) {

    };
});

Edit Controller

After that, edit the controller. We will divide the controller into some parts.

Get All Data

To display all data we create index method with request parameter. Thus method will return JSON response data to the frontend.

/**
     * Display a listing of the resource.
     *
     * @param Request $request
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $draw = intval($request->draw);
        $start = intval($request->start);
        $length = intval($request->length);
        $columns = $request->columns;
        $order = $request->order;
        $search = $request->search;
        $search = $search['value'];

        $col = '';
        $dir = "";

        $valid_columns = [];

        for ($i = 0; $i < count($columns); $i++) {
            if ($columns[$i]['data'] != "action") {
                $valid_columns[] = $columns[$i]['data'];
            }
            if (!empty($order)) {
                if ($order[0]['column'] == $i) {
                    $col = $columns[$i]['data'];
                    $dir = $order[0]['dir'];
                }
            }
        }

        if ($dir != "asc" && $dir != "desc") {
            $dir = "desc";
        }

        $query = DB::table("employees");

        if ($order != null) {
            $query->orderBy($col, $dir);
        }

        if (!empty($search)) {
            $x = 0;
            foreach ($valid_columns as $term) {
                if ($x == 0) {
                    $query->where($term, "LIKE", "%$search%");
                } else {
                    $query->orWhere($term, "LIKE", "%$search%");
                }
                $x++;
            }
        }
        $query->offset($start)->limit($length);
        $employees = $query->get();
        $data = [];
        foreach ($employees as $rows) {
            $id = $rows->id;
            $data[] = [
                "name" => $rows->name,
                "position" => $rows->position,
                "office" => $rows->office,
                "age" => $rows->age,
                "start_date" => date('d-m-Y', strtotime($rows->start_date)),
                "salary" => number_format($rows->salary, 2),
                "action" => '<button onclick="editTable('.$id.')"  data-id="'.$id.'" class="btn btn-sm btn-warning mr-1">Edit</button>
                 <button onclick="deleteRow('.$id.')" data-id="'.$id.'" class="btn btn-sm btn-danger mr-1">Delete</button>'
            ];
        }

        $total_employees = $this->totalEmployees();
        $output = array(
            "draw" => $draw,
            "recordsTotal" => $total_employees,
            "recordsFiltered" => $total_employees,
            "data" => $data
        );

        return  response()->json($output);
    }

Store data to the database

Next, to store data to database we create a store method to store data to database.

/**
     * Store a newly created resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        try {
            $id = $request->id;
            if ($id) {
                $this->update($request, $id);

                return response()->json([
                    "error" => false,
                    "msg" => "Data updated!"
                ]);
            } else {
                $employee = new Employee();
                $employee->name = $request->name;
                $employee->position = $request->position;
                $employee->office = $request->office;
                $employee->age = $request->age;
                $employee->salary = $request->salary;
                $employee->start_date = date("Y-m-d", strtotime($request->startDate));
                $employee->save();

                return response()->json([
                    "error" => false,
                    "msg" => "Data saved!"
                ]);
            }
        } catch (\Exception $e) {
            return response()->json([
                "error" => true,
                "msg" => $e->getMessage()
            ]);
        }
    }

Get Single Data

This method is used when editing the data. We need to get single data that we want to edit.

/**
     * Display the specified resource.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        $employee = Employee::where("id", "=", $id)
            ->selectRaw("
                id
                ,name
                ,position
                ,office
                ,age
                ,salary
                ,start_date
            ")
            ->first();
        return response()->json([
            "row" => $employee
        ]);
    }

Delete Data

For delete data we create destroy method with the parameter is id from the single row.

/**
     * Remove the specified resource from storage.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        try {
            Employee::where("id", "=", $id)->delete();
            return response()->json([
                "error" => false,
                "msg" => "Data deleted successfuly!"
            ]);
        } catch (\Exception $e) {
            return response()->json([
                "error" => true,
                "msg" => $e->getMessage()
            ]);
        }
    }

Here is all code from DataTableController.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\Employee;
use PhpParser\Node\Stmt\Catch_;

class DatatableController extends Controller
{
    public function indexView()
    {
        return view("crud.datatable");
    }

    /**
     * Display a listing of the resource.
     *
     * @param Request $request
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $draw = intval($request->draw);
        $start = intval($request->start);
        $length = intval($request->length);
        $columns = $request->columns;
        $order = $request->order;
        $search = $request->search;
        $search = $search['value'];

        $col = '';
        $dir = "";

        $valid_columns = [];

        for ($i = 0; $i < count($columns); $i++) {
            if ($columns[$i]['data'] != "action") {
                $valid_columns[] = $columns[$i]['data'];
            }
            if (!empty($order)) {
                if ($order[0]['column'] == $i) {
                    $col = $columns[$i]['data'];
                    $dir = $order[0]['dir'];
                }
            }
        }

        if ($dir != "asc" && $dir != "desc") {
            $dir = "desc";
        }

        $query = DB::table("employees");

        if ($order != null) {
            $query->orderBy($col, $dir);
        }

        if (!empty($search)) {
            $x = 0;
            foreach ($valid_columns as $term) {
                if ($x == 0) {
                    $query->where($term, "LIKE", "%$search%");
                } else {
                    $query->orWhere($term, "LIKE", "%$search%");
                }
                $x++;
            }
        }
        $query->offset($start)->limit($length);
        $employees = $query->get();
        $data = [];
        foreach ($employees as $rows) {
            $id = $rows->id;
            $data[] = [
                "name" => $rows->name,
                "position" => $rows->position,
                "office" => $rows->office,
                "age" => $rows->age,
                "start_date" => date('d-m-Y', strtotime($rows->start_date)),
                "salary" => number_format($rows->salary, 2),
                "action" => '<button onclick="editTable('.$id.')"  data-id="'.$id.'" class="btn btn-sm btn-warning mr-1">Edit</button>
                 <button onclick="deleteRow('.$id.')" data-id="'.$id.'" class="btn btn-sm btn-danger mr-1">Delete</button>'
            ];
        }

        $total_employees = $this->totalEmployees();
        $output = array(
            "draw" => $draw,
            "recordsTotal" => $total_employees,
            "recordsFiltered" => $total_employees,
            "data" => $data
        );

        return  response()->json($output);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        try {
            $id = $request->id;
            if ($id) {
                $this->update($request, $id);

                return response()->json([
                    "error" => false,
                    "msg" => "Data updated!"
                ]);
            } else {
                $employee = new Employee();
                $employee->name = $request->name;
                $employee->position = $request->position;
                $employee->office = $request->office;
                $employee->age = $request->age;
                $employee->salary = $request->salary;
                $employee->start_date = date("Y-m-d", strtotime($request->startDate));
                $employee->save();

                return response()->json([
                    "error" => false,
                    "msg" => "Data saved!"
                ]);
            }
        } catch (\Exception $e) {
            return response()->json([
                "error" => true,
                "msg" => $e->getMessage()
            ]);
        }
    }

    /**
     * Display the specified resource.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        $employee = Employee::where("id", "=", $id)
            ->selectRaw("
                id
                ,name
                ,position
                ,office
                ,age
                ,salary
                ,start_date
            ")
            ->first();
        return response()->json([
            "row" => $employee
        ]);
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        $employee = Employee::where("id", "=", $id)->first();
        if ($employee) {
            $employee->name = $request->name;
            $employee->position = $request->position;
            $employee->office = $request->office;
            $employee->age = $request->age;
            $employee->salary = $request->salary;
            $employee->start_date = date("Y-m-d", strtotime($request->startDate));
            $employee->save();
        }
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        try {
            Employee::where("id", "=", $id)->delete();
            return response()->json([
                "error" => false,
                "msg" => "Data deleted successfuly!"
            ]);
        } catch (\Exception $e) {
            return response()->json([
                "error" => true,
                "msg" => $e->getMessage()
            ]);
        }
    }

    //Get total employee
    private function totalEmployees()
    {
        $total = DB::table("employees")->count();
        return $total;
    }
}

Sponsored Links
Hi, my name is I Wayan Tisna Adi Muliarta. You can call me Tisna. I am the author of the Coding Of Cents

Leave a Reply

Your email address will not be published. Required fields are marked *

thirteen + six =

Back To Top