How to Datatables AJAX Pagination with Search and Sort in Laravel 8

With pagination, it is easier to display a huge list of data on the page.

You can create pagination with and without AJAX.

There are many jQuery plugins are available for adding pagination. One of them is DataTables.

In this tutorial, I show how you can add Datatables AJAX pagination without the Laravel package in Laravel 8.

Contents

  1. Database Configuration
  2. Create Table
  3. Download
  4. Model
  5. Route
  6. Controller
  7. View
  8. Demo
  9. Conclusion

1. Database Configuration

Open .env file.

Specify the host, database name, username, and password.

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

2. Create Table

  • Create a new table Employees using migration and add some records.
php artisan make:migration create_employees_table
  • Now, navigate to database/migrations/ folder from the project root.
  • Find a PHP file that ends with create_employees_table and open it.
  • Define the table structure in the up() method.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->timestamps();
    });
}
  • Run the migration –
php artisan migrate
  • The table is been created and add some records in it.

3. Download

  • Download DataTables library from here and also download the jQuery library.
  • Extract the downloaded files in public/ folder.
  • Also, copied the jQuery library in public/ folder.
  • You can also use CDN –
<!-- Datatable CSS -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css"/>

<!-- jQuery Library -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

<!-- Datatable JS -->
<script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>

4. Model

  • Create Employees Model.
php artisan make:model Employees
  • Open app/Models/Employees.php file.
  • Specify mass assignable Model attributes – username, name, and email using the $filliable property.

Completed Code

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email'
    ];
}

5. Route

  • Open routes/web.php file.
  • Define 2 routes –
    • / – Load index view.
    • /getEmployees – This is used to send AJAX POST request to fetch the datatables data.
<?php
use Illuminate\Support\Facades\Route; 
use App\Http\Controllers\EmployeesController; 

Route::get('/', [EmployeesController::class, 'index']); 
Route::get('/getEmployees', [EmployeesController::class, 'getEmployees'])->name('getEmployees');

6. Controller

  • Create EmployeesController Controller.
php artisan make:controller EmployeesController
  • Open app/Http/Controllers/EmployeesController.php file.
  • Import Employees Model.

Create 2 methods –

  • index() – Load index view.
  • getEmployees() – This method is to handle DataTables AJAX request.

Read DataTables values and assign them to the variables.

Count total records with and without search filter from 'employees' table and assign to $totalRecords and $totalRecordswithFilter variable.

Fetch records from 'employees' table and assign to $records variable.

Loop on the fetched data and initialize $data Array with keys that are specified in the 'columns' option while initializing DataTables.

Initialize $response Array with required values.

Return $response Array in JSON format.

Completed Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Employees;

class EmployeesController extends Controller
{
     public function index(){

         // Load index view
         return view('index');
     }

     // Fetch records
     public function getEmployees(Request $request){

        ## Read value
        $draw = $request->get('draw');
        $start = $request->get("start");
        $rowperpage = $request->get("length"); // Rows display per page

        $columnIndex_arr = $request->get('order');
        $columnName_arr = $request->get('columns');
        $order_arr = $request->get('order');
        $search_arr = $request->get('search');

        $columnIndex = $columnIndex_arr[0]['column']; // Column index
        $columnName = $columnName_arr[$columnIndex]['data']; // Column name
        $columnSortOrder = $order_arr[0]['dir']; // asc or desc
        $searchValue = $search_arr['value']; // Search value

        // Total records
        $totalRecords = Employees::select('count(*) as allcount')->count();
        $totalRecordswithFilter = Employees::select('count(*) as allcount')->where('name', 'like', '%' .$searchValue . '%')->count();

        // Fetch records
        $records = Employees::orderBy($columnName,$columnSortOrder)
               ->where('employees.name', 'like', '%' .$searchValue . '%')
              ->select('employees.*')
              ->skip($start)
              ->take($rowperpage)
              ->get();

        $data_arr = array();

        foreach($records as $record){
           $id = $record->id;
           $username = $record->username;
           $name = $record->name;
           $email = $record->email;

           $data_arr[] = array(
               "id" => $id,
               "username" => $username,
               "name" => $name,
               "email" => $email
           );
        }

        $response = array(
           "draw" => intval($draw),
           "iTotalRecords" => $totalRecords,
           "iTotalDisplayRecords" => $totalRecordswithFilter,
           "aaData" => $data_arr
        );

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

7. View

Create index.blade.php file in resources/views/ folder.

Include DataTables and jQuery library.

Create <table id='empTable' >.

jQuery Script –

  • Initialize DataTables on #empTable.
  • Set processing: true, serverSide: true.
  • Set AJAX URL to {{route('getEmployees')}}.
  • With 'columns' option specifies key names that need to be read from the AJAX response.

Completed Code

<!DOCTYPE html>
<html>
<head>
     <title>Datatables AJAX pagination with Search and Sort in Laravel 8</title>

     <!-- Meta -->
     <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
     <meta charset="utf-8">

     <!-- Datatable CSS -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.25/css/jquery.dataTables.min.css"/>

     <!-- jQuery Library -->
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

     <!-- Datatable JS -->
     <script src="https://cdn.datatables.net/1.10.25/js/jquery.dataTables.min.js"></script>

</head>
<body>

    <table id='empTable' width='100%' border="1" style='border-collapse: collapse;'>
        <thead>
            <tr>
                 <td>S.no</td>
                 <td>Username</td>
                 <td>Name</td>
                 <td>Email</td>
            </tr>
         </thead>
     </table>

     <!-- Script -->
     <script type="text/javascript"> 
     $(document).ready(function(){

         // DataTable
        $('#empTable').DataTable({
             processing: true,
             serverSide: true,
             ajax: "{{route('getEmployees')}}",
             columns: [
                 { data: 'id' },
                 { data: 'username' },
                 { data: 'name' },
                 { data: 'email' },
             ]
         });

      });
      </script>
</body>
</html>

8. Demo

View Demo


9. Conclusion

Make sure to return the DataTables response in a defined format otherwise, the data not load.

If you caught any error while adding DataTables then use the browser network tab to debug by viewing the AJAX request response.

You can check out this tutorial to learn how to add custom filters in DataTable.

If you found this tutorial helpful then don't forget to share.

Original article source at: https://makitweb.com/

#laravel #datatable #ajax 

What is GEEK

Buddha Community

How to Datatables AJAX Pagination with Search and Sort in Laravel 8
Seamus  Quitzon

Seamus Quitzon

1595201363

Php how to delete multiple rows through checkbox using ajax in laravel

First thing, we will need a table and i am creating products table for this example. So run the following query to create table.

CREATE TABLE `products` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
 `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Next, we will need to insert some dummy records in this table that will be deleted.

INSERT INTO `products` (`name`, `description`) VALUES

('Test product 1', 'Product description example1'),

('Test product 2', 'Product description example2'),

('Test product 3', 'Product description example3'),

('Test product 4', 'Product description example4'),

('Test product 5', 'Product description example5');

Now we are redy to create a model corresponding to this products table. Here we will create Product model. So let’s create a model file Product.php file under app directory and put the code below.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $fillable = [
        'name','description'
    ];
}

Step 2: Create Route

Now, in this second step we will create some routes to handle the request for this example. So opeen routes/web.php file and copy the routes as given below.

routes/web.php

Route::get('product', 'ProductController@index');
Route::delete('product/{id}', ['as'=>'product.destroy','uses'=>'ProductController@destroy']);
Route::delete('delete-multiple-product', ['as'=>'product.multiple-delete','uses'=>'ProductController@deleteMultiple']);

#laravel #delete multiple rows in laravel using ajax #laravel ajax delete #laravel ajax multiple checkbox delete #laravel delete multiple rows #laravel delete records using ajax #laravel multiple checkbox delete rows #laravel multiple delete

I am Developer

1597727551

Laravel 7 Crud using Datatables

yajra datatables crud with ajax in laravel 7. In this post, i will show you how to create crud using datatable in laravel with ajax and model.

Now, i am going to show you how to install and use datatables in laravel 7 application. i will use jquery ajax crud with modals using datatables js in laravel 7. i will write easy code of jquery ajax request for crud with yajra datatable.

Laravel 7 DataTable CRUD Example

Use the below steps and create yajra DataTables crud with ajax in laravel:

Step 1: Install Laravel App For DataTable Crud
Step 2: Configuration .evn file
Step 3: Run Migration
Step 4: Install Yajra DataTables Package
Step 5: Add Fake Data into Database table
Step 6: Add Datatable Ajax Route
Stpe 7: Create DataTableController
Step 8: Create Ajax Datatable Blade View
Step 9: Start Development Server

https://www.tutsmake.com/laravel-7-6-install-yajra-datatables-example-tutorial/

#laravel 6 yajra datatables #yajra datatables laravel 6 example #laravel-datatables crud #yajra datatables laravel 7 #laravel 7 datatables #yajra datatables laravel

I am Developer

1617089618

Laravel 8 Tutorial for Beginners

Hello everyone! I just updated this tutorial for Laravel 8. In this tutorial, we’ll go through the basics of the Laravel framework by building a simple blogging system. Note that this tutorial is only for beginners who are interested in web development but don’t know where to start. Check it out if you are interested: Laravel Tutorial For Beginners

Laravel is a very powerful framework that follows the MVC structure. It is designed for web developers who need a simple, elegant yet powerful toolkit to build a fully-featured website.

Recommended:-Laravel Try Catch

#laravel 8 tutorial #laravel 8 tutorial crud #laravel 8 tutorial point #laravel 8 auth tutorial #laravel 8 project example #laravel 8 tutorial for beginners

I am Developer

1599536794

Laravel 8 New Features | Release Notes - Tuts Make

In this post, i will show you what’s new in laravel 8 version.

#What’s new in Laravel 8?

  • 1 - Change Path Of Default Models Directory
  • 2 - Removed Controllers Namespace Prefix
  • 3 - Enhancements on php artisan serve
  • 4 - Enhanced Rate Limiting
  • 5 - Enhanced on Route Caching
  • 6 - Update on Pagination Design
  • 8 - Dynamic Blade Componenets
  • 7 - Update Syntax for Closure Based Event Listeners
  • 8 - Queueable Model Event Listeners
  • 9 - Maintenance mode: secret access
  • 10 - Maintenance mode: pre-rendered page
  • 11 - Queued job batching
  • 12 - Queue backoff()
  • 13 - Laravel Factory

https://www.tutsmake.com/laravel-8-new-features-release-notes/

#laravel 8 features #laravel 8 release date #laravel 8 tutorial #news - laravel 8 new features #what's new in laravel 8 #laravel 8 release notes

I am Developer

1597475640

Laravel 7 Full Text Search MySQL

Here, I will show you how to create full text search in laravel app. You just follow the below easy steps and create full text search with mysql db in laravel.

Laravel 7 Full Text Search Mysql

Let’s start laravel full-text search implementation in laravel 7, 6 versions:

  1. Step 1: Install Laravel New App
  2. Step 2: Configuration DB .evn file
  3. Step 3: Run Migration
  4. Step 4: Install Full Text Search Package
  5. Step 5: Add Fake Records in DB
  6. Step 6: Add Routes,
  7. Step 7: Create Controller
  8. Step 8: Create Blade View
  9. Step 9: Start Development Server

https://www.tutsmake.com/laravel-full-text-search-tutorial/

#laravel full text search mysql #laravel full text search query #mysql full text search in laravel #full text search in laravel 6 #full text search in laravel 7 #using full text search in laravel