Laravel 5.8 Tutorial - Datatables Dropdown Filter Server-side using Ajax

Laravel 5.8 Tutorial - Datatables Dropdown Filter Server-side using Ajax

In this post, we will learn how to Search or Filter Laravel Datatables data using Ajax jQuery with Individual column searching. Here you can find a complete process of Individual column searching with server side process in Laravel data tables using yajra from scratch.

Originally published at https://www.webslesson.info

This is one more post on Laravel 5.8 with Datatables and in this post we will cover How can we implement Laravel Datatables Individual Column Searching using Ajax. In some of our previous post, we have already covered topic like how to add custom search filter in Laravel Datatables and How to make Daterange search filter in Laravel Datatables. Now here we have come with new topic in Laravel 5.8 framework with Datatables and here we will show you how to add Individual column dropdown search filter in Laravel DataTables.

Here we will create dropdown search filter in Laravel DataTables with serve-side processing of data that means all searching or filtering of data will be process at server side and display result on web page without refresh of web page because here we will use Ajax with Laravel 5.8 and Datatables. For implement DataTables with Laravel 5.8 here we will use yajra/laravel-datatables-oracle package. By using this package we can use jQuery DataTables in Laravel 5.8 framework.

For learn individual column searching or filtering of DataTables data, we will add dropdown list in one of the column of DataTables and then after by using jQuery and Ajax we will filter DataTables data. For this here we will take and example of display product data on DataTables, now we want to filter this product data based on category of product. So, we will make category dropdown list in category column, and based on value of category we will filter or search product data in DataTables with Laravel 5.8 using Ajax jQuery. Below you can find step by step process for Laravel 5.8 DataTables Individual column searching or filtering of data using Ajax jquery.

  • Make Tables in Mysql Database
  • Install Laravel 5.8 framework
  • Install yajra/laravel-datatables-oracle package
  • Make Database connection in Laravel 5.8
  • Create Controllers in Laravel 5.8 application
  • Create Blade view file in Laravel 5.8
  • Set Route of Controllers method
  • Run Laravel 5.8 Application

Make Tables in Mysql Database

First you have make table in your mysql database, for this you have to run following sql script in your mysql database. It will make category table and product table in your database. Here we will fetch data from this two table by join table with category_id primary key in category table and category foreign key in product table.

--
-- Database: `testing1`
--


-- -- Table structure for table category --

CREATE TABLE IF NOT EXISTS category (  category_id int(11) NOT NULL,  category_name varchar(250) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- -- Dumping data for table category --

INSERT INTO category (category_id, category_name) VALUES (1, 'Mobiles'), (2, 'Computers'), (3, 'Clothing'), (4, 'Beauty Item'), (5, 'Sports Item'), (6, 'Toys Item'), (7, 'Books'), (8, 'Entertainment Item');


-- -- Table structure for table product --

CREATE TABLE IF NOT EXISTS product (  id int(11) NOT NULL,  category int(11) NOT NULL,  name varchar(250) NOT NULL,  price double(10,2) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;

-- -- Dumping data for table product --

INSERT INTO product (id, category, name, price) VALUES (1, 1, 'Save on BLU Advance 5.5 HD', 74.99), (2, 2, 'Dell Inspiron 15.6" Gaming Laptop', 860.00), (3, 3, 'Women''s Slim Sleeveless', 69.00), (4, 4, 'Andis 1875-Watt Fold-N-Go Ionic Hair Dryer', 17.00), (5, 5, 'GM Ripple Cricket Grip, Set Of 3', 66.00), (6, 6, 'Barbie Fashions and Accessories', 12.00), (7, 7, 'The Ministry of Utmost Happiness', 6.00), (8, 8, 'The Great Gatsby (3D)', 8.00), (9, 1, 'iVooMi Me 1+', 49.00), (10, 2, 'Apple MacBook Air MQD32HN/A 13.3-inch Laptop 2017', 896.00), (11, 3, 'Balenzia Premium Mercerised Cotton Loafer Socks', 5.00), (12, 4, 'Organix Mantra Lemon Cold Pressed Essential Oil', 4.50), (13, 5, 'SpeedArm Cricket Ball Thrower', 15.00), (14, 6, 'Mattel Bounce Off Game, Multi Color', 10.00), (15, 7, 'Seven Days With Her Boss', 5.00), (16, 8, 'Supernatural Season 1-9 DVD', 22.00), (17, 1, 'InFocus Turbo 5', 189.00), (18, 2, 'HP 15-bg008AU 15.6-inch Laptop , Jack Black', 350.00), (19, 3, 'Seven Rocks Men''s V-Neck Cotton Tshirt', 12.00), (20, 4, 'Exel Elixir Sublime Antioxidant Serum Cream', 55.00), (21, 5, 'Gray Nicolls Bat Repair Kit', 9.00), (22, 6, 'Think Fun Rush Hour, Multi Color', 22.00), (23, 7, 'Pregnancy Notes: Before, During & After', 5.00), (24, 8, 'Sherlock Season - 4', 15.00), (25, 1, 'Vivo Y53', 105.00), (26, 2, 'Dell Inspiron 15-3567 15.6-inch Laptop', 356.00), (27, 3, 'Fastrack Sport Sunglasses (Black) (P222GR1)', 14.00), (28, 4, 'Exel Lotion with stabilized Tea Tree Oil', 28.00), (29, 5, 'Burn Vinyl Hexagonal Dumbbell', 45.00), (30, 6, 'Cup Cake Surprise Princess', 8.00), (31, 7, 'Word Power Made Easy', 2.00), (32, 8, 'Star Wars: The Force Awakens', 5.00), (33, 1, 'Lenovo Vibe K5 (Gold, VoLTE update)', 65.00), (34, 2, 'Lenovo 110 -15ACL 15.6-inch Laptop , Black', 225.00), (35, 3, 'Zacharias Ankle Socks Pack of 12 Pair', 5.00), (36, 4, 'Exel SUNSCREEN Broad Spectrum UVA & UVB', 26.00), (37, 5, 'Burn 500124 Inter Lock Mat (Black)', 24.00), (38, 6, 'Toyshine Devis Boy 9', 10.00), (39, 7, 'Think and Grow Rich', 2.50), (40, 8, 'The Jungle Book', 10.00);

-- -- Indexes for dumped tables --

-- -- Indexes for table category -- ALTER TABLE category  ADD PRIMARY KEY (category_id);

-- -- Indexes for table product -- ALTER TABLE product  ADD PRIMARY KEY (id);

-- -- AUTO_INCREMENT for dumped tables --

-- -- AUTO_INCREMENT for table category -- ALTER TABLE category  MODIFY category_id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9; -- -- AUTO_INCREMENT for table product -- ALTER TABLE product  MODIFY id int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=41;

Install Laravel 5.8 framework

Now we want to download and install Laravel 5.8 framework in our computer. For this we have to go command prompt in which first we want to run composer command because it has manage Laravel library dependancy and after this we have to write following command. This command will download and install Laravel 5.8 framework in your local computer.

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

Install yajra/laravel-datatables-oracle package

Here we want to use DataTables with Laravel. For this use DataTables with Laravel we want to download and install yajra laravel datatables package. By using this package we can use DataTables in Laravel framework. For this we have to go command prompt and write following command.

composer require yajra/laravel-datatables-oracle

This command will download yajra laravel datatables package, now we want to publish this package in Laravel application. For this we have to go config/app.php and in this file we have to define autoload service providers and aliase class details.

'providers' => [

       ............

       Yajra\Datatables\DatatablesServiceProvider::class,

   ],

   'aliases' => [                 ............                'Datatables' => Yajra\Datatables\Facades\Datatables::class,

   ],

Make Database connection in Laravel 5.8

After this we want to make database connection in this Laravel 5.8 application, for this we have to open .env file and in this we have to define Mysql database configuration.

........................

DB_CONNECTION=mysql DB_HOST=localhost DB_PORT=3306 DB_DATABASE=testing DB_USERNAME=root DB_PASSWORD=

........................

Create Controllers in Laravel 5.8 application

Now we want to make controllers in Laravel 5.8 application for handle http request. For this we have to go command prompt and write following command.

php artisan make:controller ColumnSearchingController

This command will create ColumnSearchingController.php controller class in app/Http/Controllers folder. In this class first we have write use DB; statement. By using this statement we can perform database related operation. In this class we have make following method.

index(Request $request) - This the root method of this class. This method has load column_searching.blade.php file in browser with the category data for fill category dropdown list box for filter data. This method has also received ajax request for load all data in Datatable or load filter data in Datatable. This method has also send data from datatables also.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use DB;

class ColumnSearchingController extends Controller {    function index(Request $request)    {     if(request()->ajax())     {      if($request->category)      {       $data = DB::table('product')         ->join('category', 'category.category_id', '=', 'product.category')         ->select('product.id', 'product.name', 'category.category_name', 'product.price')         ->where('product.category', $request->category);      }      else      {       $data = DB::table('product')         ->join('category', 'category.category_id', '=', 'product.category')         ->select('product.id', 'product.name', 'category.category_name', 'product.price');      }

     return datatables()->of($data)->make(true);     }

    $category = DB::table('category')        ->select("*")        ->get();

    return view('column_searching', compact('category'));    } }

?>

Create Blade view file in Laravel 5.8

View file is mainly used for display html output data on web page. In laravel we have store view file in resources/views/column_searching.blade.php. In this blade view file we have already imported required library like jQuery, Bootstrap and jquery DataTables. Here first it has make category dropdown list box by using category data from controller index method. In this page we have also write jquery and Ajax code for initialize jQuery DataTables plugin and dropdown search filter of DataTables data also.

<html>
 <head>
 <meta name="viewport" content="width=device-width, initial-scale=1">
 <title>Laravel 5.8 Tutorial - Datatables Individual Column Searching using Ajax</title>
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
 <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
 <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script> 
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
 <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 </head>
 <body>
 <div class="container">   
     <br />
    <h3 align="center">Laravel 5.8 Tutorial - Datatables Individual Column Searching using Ajax</h3>
    <br />
  <div class="table-responsive">
   <table class="table table-bordered table-striped" id="product_table">
    <thead>
     <tr>
      <th>Sr. No.</th>
      <th>Product Name</th>
      <th>
       <select name="category_filter" id="category_filter" class="form-control">
        <option value="">Select Category</option>
        @foreach($category as $row)
        <option value="{{ $row->category_id }}">{{ $row->category_name }}</option>
        @endforeach
       </select>
      </th>
      <th>Product Price</th>
     </tr>
    </thead>
   </table>
  </div>
  <br />
  <br />
 </div>
 </body>
</html>

<script> $(document).ready(function(){   fetch_data();   function fetch_data(category = '') {   $('#product_table').DataTable({    processing: true,    serverSide: true,    ajax: {     url:"{{ route('column-searching.index') }}",     data: {category:category}    },    columns:[     {      data: 'id',      name: 'id'     },     {      data: 'name',      name: 'name'     },     {      data: 'category_name',      name: 'category_name',      orderable: false     },     {      data:'price',      name:'price'     }    ]   }); }   $('#category_filter').change(function(){   var category_id = $('#category_filter').val();     $('#product_table').DataTable().destroy();     fetch_data(category_id); });

}); </script>

Set Route of Controllers method

Once all code is ready, now we want to set route of controller method. For this we have to go routes/web.php file and under this file we have to define route for controller method.

<?php

Route::resource('column-searching', 'ColumnSearchingController');

?>

Run Laravel 5.8 Application

Lastly, we want to run Laravel 5.8 application. For this again we want to go command prompt and write following command.

php artisan serve

This command will start Laravel 5.8 application and give base url of your application. For run above code, we have to write http://127.0.0.1:8000/column-searching. It will execute above code. So this is complete step by step process of server side processing of Individual Datatables column searching or filtering of Data in Laravel 5.8 using Ajax. So, test this script and learn something new in Laravel framework.


laravel ajax database web-development

What's new in Bootstrap 5 and when Bootstrap 5 release date?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Random Password Generator Online

HTML Color Picker online | HEX Color Picker | RGB Color Picker

Top Ajax Development Companies | Hire Ajax Developers

Here is the list of proficient AJAX development service providers. TopDevelopers has keenly fulfilled your requirement and brought in the list of expert AJAX developers in the market to assist you in your next big project. The top listed AJAX...

Top Laravel Development Companies Reviews

Among the list of Best Laravel development companies, TopDevelopers has chosen the most acclaimed laravel developers for the visitors so as to make them avail the extremely wonderful service ever offered. The web development companies listed here...

Symfony Or Laravel: Prolonged Battle in Web Solutions Development Persists

Many popular languages for web development have their 'default' framework, such as Ruby on Rails ... PHP Frameworks: Choosing Between Symfony and Laravel ... class PostRepository extends EntityRepository { public function persist(Post $post) ... and it helps to build your application following SOLID design principles

Hire Dedicated eCommerce Web Developers | Top eCommerce Web Designers

Build your eCommerce project by hiring our expert eCommerce Website developers. Our Dedicated Web Designers develop powerful & robust website in a short span of time.

Laravel Framework Development Services | Laravel Web Development

CMARIX is leading Laravel Web application Development Service Provider with the track record of implementing diverse high-performance Laravel web applications with skilled Laravel developer team for clients across the globe<a href="https://www.cmarix.com/laravel-framework-development-services.html?utm_source=SB" target="_blank">.Continue</a>