jQuery Datatables with Laravel, MySQL and AJAX Example

Today, We want to share with you laravel datatables ajax.In this post we will show you laravel datatables server side, hear for how to delete data using ajax in laravel with datatables we will give you demo and example for implement.In this post, we will learn about laravel datatables ajax pagination with an example.

Laravel 6 Ajax Crud Tutorial Using DataTables From Scratch

Laravel DataTables Ajax Crud


  • Install Laravel 6 Setup
  • Setup database
  • Database Migration
  • Install Yajra DataTables
  • Create Route, Controller & Blade View
  • Start Development Server
  • Conclusion

Install Laravel

Step 1:

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

setup .env file

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

Database Migration

php artisan make:model Fruit --migration

path\to\your\project\database\migrations]2019_11_12_023506_create_fruits_table.php

<?php
 
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
 
class CreateFruitsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('fruits', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->string('fruit_code');
            $table->string('description');
            $table->timestamps();
        });
    }
 
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('fruits');
    }
}

and then run this cmd bellow

php artisan migrate

Install Yajra Datatables Package in Laravel

step 2:

composer require yajra/laravel-datatables-oracle

config/app.php

'providers' => [
     Yajra\Datatables\DatatablesServiceProvider::class,
 ],
 
 'aliases' => [
    'Datatables' => Yajra\Datatables\Facades\Datatables::class,
 ]  

publish vendor run

php artisan vendor:publish --tag=datatables

Open routes/web.php file

Route::get('fruit-list', '[email protected]');
Route::get('fruit-list/{id}/edit', '[email protected]');
Route::post('fruit-list/store', '[email protected]');
Route::get('fruit-list/delete/{id}', '[email protected]');

Create Controller

php artisan make:controller FruitController --resource

app/Http/Controllers/FruitController.php

<?php
  
namespace App\Http\Controllers;
  
use App\Fruit;
use Illuminate\Http\Request;
use Redirect,Response;
  
class FruitController extends Controller
{
/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    if(request()->ajax()) {
        return datatables()->of(Fruit::select('*'))
        ->addColumn('action', 'DataTables.action')
        ->rawColumns(['action'])
        ->addIndexColumn()
        ->make(true);
    }
    return view('list');
}
  
  
/**
 * Store a newly created resource in storage.
 *
 * @param  \Illuminate\Http\Request  $request
 * @return \Illuminate\Http\Response
 */
public function store(Request $request)
{  
    $fruitId = $request->fruit_id;
    $fruit   =   Fruit::updateOrCreate(['id' => $fruitId],
                ['title' => $request->title, 'fruit_code' => $request->fruit_code, 'description' => $request->description]);        
    return Response::json($fruit);
}
  
  
/**
 * Show the form for editing the specified resource.
 *
 * @param  \App\Fruit  $fruit
 * @return \Illuminate\Http\Response
 */
public function edit($id)
{   
    $where = array('id' => $id);
    $fruit  = Fruit::where($where)->first();
  
    return Response::json($fruit);
}
  
  
/**
 * Remove the specified resource from storage.
 *
 * @param  \App\Fruit  $fruit
 * @return \Illuminate\Http\Response
 */
public function destroy($id)
{
    $fruit = Fruit::where('id',$id)->delete();
  
    return Response::json($fruit);
}
}

Create Blade View : action.blade.php file

<a href="javascript:void(0)" data-toggle="tooltip"  data-id="{{ $id }}" data-original-title="Edit" class="edit btn btn-success edit-fruit">
    Edit
</a>
<a href="javascript:void(0);" id="delete-fruit" data-toggle="tooltip" data-original-title="Delete" data-id="{{ $id }}" class="delete btn btn-danger">
    Delete
</a>

resources/views/list.blade.php

<!DOCTYPE html>
  
<html lang="en">
<head>
<!-- CSRF Token -->
<meta name="csrf-token" content="{{ csrf_token() }}">
<title>Laravel DataTable Ajax Crud Tutorial - Tuts Make</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
<link  href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.js"></script>  
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.19.0/jquery.validate.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js"></script>
</head>
<body>
  
<div class="container">
<h2>Laravel DataTable Ajax Crud Tutorial - <a href="https://www.tutsmake.com" target="_blank" rel="noopener">TutsMake</a></h2>
<br>
<a href="https://www.tutsmake.com/how-to-install-yajra-datatables-in-laravel/" class="btn btn-secondary">Back to Post</a>
<a href="javascript:void(0)" class="btn btn-info ml-3" id="create-new-fruit">Add New</a>
<br><br>
  
<table class="table table-bordered table-striped" id="laravel_datatable">
   <thead>
      <tr>
         <th>ID</th>
         <th>S. No</th>
         <th>Title</th>
         <th>Fruit Code</th>
         <th>Description</th>
         <th>Created at</th>
         <th>Action</th>
      </tr>
   </thead>
</table>
</div>
  
<div class="modal fade" id="ajax-fruit-modal" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
    <div class="modal-header">
        <h4 class="modal-title" id="fruitCrudModal"></h4>
    </div>
    <div class="modal-body">
        <form id="fruitForm" name="fruitForm" class="form-horizontal">
           <input type="hidden" name="fruit_id" id="fruit_id">
            <div class="form-group">
                <label for="name" class="col-sm-2 control-label">Title</label>
                <div class="col-sm-12">
                    <input type="text" class="form-control" id="title" name="title" placeholder="Enter Tilte" value="" maxlength="50" required="">
                </div>
            </div> 
            <div class="form-group">
                <label for="name" class="col-sm-2 control-label">Fruit Code</label>
                <div class="col-sm-12">
                    <input type="text" class="form-control" id="fruit_code" name="fruit_code" placeholder="Enter Tilte" value="" maxlength="50" required="">
                </div>
            </div>
  
            <div class="form-group">
                <label class="col-sm-2 control-label">Description</label>
                <div class="col-sm-12">
                    <input type="text" class="form-control" id="description" name="description" placeholder="Enter Description" value="" required="">
                </div>
            </div>
            <div class="col-sm-offset-2 col-sm-10">
             <button type="submit" class="btn btn-primary" id="btn-save" value="create">Save changes
             </button>
            </div>
        </form>
    </div>
    <div class="modal-footer">
         
    </div>
</div>
</div>
</div>
</body>
 
</html>

Script logic

Read Also:  Image Crop and Upload using jQuery and PHP

<script>
 var SITEURL = '{{URL::to('')}}';
 $(document).ready( function () {
   $.ajaxSetup({
      headers: {
          'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
      }
  });
  $('#laravel_datatable').DataTable({
         processing: true,
         serverSide: true,
         ajax: {
          url: SITEURL + "fruit-list",
          type: 'GET',
         },
         columns: [
                  {data: 'id', name: 'id', 'visible': false},
                  {data: 'DT_RowIndex', name: 'DT_RowIndex', orderable: false,searchable: false},
                  { data: 'title', name: 'title' },
                  { data: 'fruit_code', name: 'fruit_code' },
                  { data: 'description', name: 'description' },
                  { data: 'created_at', name: 'created_at' },
                  {data: 'action', name: 'action', orderable: false},
               ],
        order: [[0, 'desc']]
      });
 
 /*  When member click add member button */
    $('#create-new-fruit').click(function () {
        $('#btn-save').val("create-fruit");
        $('#fruit_id').val('');
        $('#fruitForm').trigger("reset");
        $('#fruitCrudModal').html("Add New Fruit");
        $('#ajax-fruit-modal').modal('show');
    });
  
   /* When click edit member */
    $('body').on('click', '.edit-fruit', function () {
      var fruit_id = $(this).data('id');
      $.get('fruit-list/' + fruit_id +'/edit', function (data) {
         $('#title-error').hide();
         $('#fruit_code-error').hide();
         $('#description-error').hide();
         $('#fruitCrudModal').html("Edit Fruit");
          $('#btn-save').val("edit-fruit");
          $('#ajax-fruit-modal').modal('show');
          $('#fruit_id').val(data.id);
          $('#title').val(data.title);
          $('#fruit_code').val(data.fruit_code);
          $('#description').val(data.description);
      })
   });
 
    $('body').on('click', '#delete-fruit', function () {
  
        var fruit_id = $(this).data("id");
        
        if(confirm("Are You sure want to delete !")){
          $.ajax({
              type: "get",
              url: SITEURL + "fruit-list/delete/"+fruit_id,
              success: function (data) {
              var oTable = $('#laravel_datatable').dataTable(); 
              oTable.fnDraw(false);
              },
              error: function (data) {
                  console.log('Error:', data);
              }
          });
        }
    }); 
   
   });
  
if ($("#fruitForm").length > 0) {
      $("#fruitForm").validate({
  
     submitHandler: function(form) {
  
      var actionType = $('#btn-save').val();
      $('#btn-save').html('Sending..');
       
      $.ajax({
          data: $('#fruitForm').serialize(),
          url: SITEURL + "fruit-list/store",
          type: "POST",
          dataType: 'json',
          success: function (data) {
  
              $('#fruitForm').trigger("reset");
              $('#ajax-fruit-modal').modal('hide');
              $('#btn-save').html('Save Changes');
              var oTable = $('#laravel_datatable').dataTable();
              oTable.fnDraw(false);
               
          },
          error: function (data) {
              console.log('Error:', data);
              $('#btn-save').html('Save Changes');
          }
      });
    }
  })
}
</script>

Start Development Server

Step 3: php artisan serve command and start your server :

 php artisan serve
 php artisan serve --port=8080  

 http://localhost:8000/fruit-list

I hope you get an idea about php laravel table.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.