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.
