Thursday 24 October 2019

Laravel 6 Tutorial on Full Text Search using Ajax



This is one more post on Laravel 6 and in this post you can find how to implement Full Text Search in Laravel 6 framework by using Ajax with Searchable Laravel package. If you wanted to learn how to make Full Text Search in you Laravel 6 application? If yes, then this post will help you can learning this topic. Here in this post we have share tutorial on How to create Full Text Search by using "nicolaslopezj/searchable" package with Ajax in Laravel 6 framework. So, User can get searchable data on web page without refresh of web page, because here we have use Ajax in Laravel 6 framework.

You have to know what is Full Text Search. So, Full Text Search is a one type of Search method, which search data on every word of request in your database. Most of Search Engines has been used Full Text Search technique for search data on every word of search request and search result on web page. It is more advance level for search a data in database.

We all know Searching of data is required in almost all dynamic website which deal with Database. So, Search feature is required for any web based application. But if this search box has full text search feature then it will display more meaningful search result to your viewers based on their search query. If you have use Datatable then it will search all word as single word, while in Full text search it will search and comperes every word for search data within database. So, here we have learn how to implement Full text search feature in Laravel 6 framework using Ajax with "nicolaslopezj/searchable" composer package.

  • Download Laravel 6 framework
  • Download and Install Searchable Package
  • Make Database Connection
  • Create Model
  • Create Controller
  • Create View Blade File
  • Set Controller Method Route
  • Run Laravel 6 Application


Download Laravel 6 framework


For make any Laravel 6 application, first we need to download and install Laravel 6 framework. So, in this step of Laravel 6 tutorial, we have to go command prompt and go directory in which we want to download load and install Laravel 6 framework. We have to write and run following command.


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


This command will downloa and intall Laravel 6 framework in define directory.

Download and Install Searchable Package


In this Laravel 6 tutorial, We need to make Full text search feature in Laravel 6 application. For this here we have use "nicolaslopezj/searchable" package. For this we have to go command prompt and run following command. This command will download and install this searchable package in Laravel 6 application.


composer require nicolaslopezj/searchable


Make Database Connection


Before making Mysql Database connection, first we need to create table in your database. For this, you have to run following SQL script.


CREATE TABLE `full_text_searches` (
  `CustomerID` int(11) NOT NULL,
  `CustomerName` varchar(250) NOT NULL,
  `Gender` varchar(30) NOT NULL,
  `Address` text NOT NULL,
  `City` varchar(250) NOT NULL,
  `PostalCode` varchar(30) NOT NULL,
  `Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `full_text_searches`
--

INSERT INTO `full_text_searches` (`CustomerID`, `CustomerName`, `Gender`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Female', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Female', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Male', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', 'Male', '120 Hanover Sq.', 'London', 'WA1 1DP', 'United Kingdom'),
(5, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'Male', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Male', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', 'Male', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'United States'),
(9, 'Paula Parente', 'Female', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'John Koskitalo', 'Male', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(39, 'Ann Devon', 'Female', '35 King George', 'London', 'WX3 6FW', 'United Kingdom'),
(38, 'Janine Labrune', 'Female', '67, rue des Cinquante Otages', 'Nantes', '44000', 'Finland'),
(37, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(36, 'Elizabeth Brown', 'Female', 'Berkeley Gardens 12 Brewery', 'London', 'WX1 6LT', 'United Kingdom'),
(30, 'Trina Davidson', 'Female', '1049 Lockhart Drive', 'Barrie', 'ON L4M 3B1', 'Canada'),
(31, 'Jeff Putnam', 'Male', 'Industrieweg 56', 'Bouvignies', '7803', 'Belgium'),
(32, 'Joyce Rosenberry', 'Female', 'Norra Esplanaden 56', 'HELSINKI', '380', 'Finland'),
(33, 'Ronald Bowne', 'Male', '2343 Shadowmar Drive', 'New Orleans', '70112', 'United States'),
(34, 'Justin Adams', 'Male', '45, rue de Lille', 'ARMENTIERES', '59280', 'France'),
(35, 'Pedro Afonso', 'Male', 'Av. dos Lusiadas, 23', 'Sao Paulo', '05432-043', 'Brazil'),
(100, 'Kathryn Segal', 'Female', 'Augsburger Strabe 40', 'Ludenscheid Gevelndorf', '58513', 'Germany'),
(101, 'Tonia Sayre', 'Female', '84 Haslemere Road', 'ECHT', 'AB32 2DY', 'United Kingdom'),
(102, 'Loretta Harris', 'Female', 'Avenida Boavista 71', 'SANTO AMARO', '4920-111', 'Portugal'),
(103, 'Sean Wong', 'Male', 'Rua Vito Bovino, 240', 'Sao Paulo-SP', '04677-002', 'Brazil'),
(104, 'Frederick Sears', 'Male', 'ul. Marysiuska 64', 'Warszawa', '04-617', 'Poland'),
(105, 'Tammy Cantrell', 'Female', 'Lukiokatu 34', 'HAMEENLINNA', '13250', 'Finland'),
(106, 'Megan Kennedy', 'Female', '1210 Post Farm Road', 'Norcross', '30071', 'United States'),
(107, 'Maria Whittaker', 'Female', 'Spresstrasse 62', 'Bielefeld Milse', '33729', 'Germany'),
(108, 'Dorothy Parker', 'Female', '32 Lairg Road', 'NEWCHURCH', 'HR5 5DR', 'United Kingdom'),
(109, 'Roger Rudolph', 'Male', 'Avenida Julio Saul Dias 78', 'PENAFIEL', '4560-470', 'Portugal'),
(110, 'Karen Metivier', 'Female', 'Rua Guimaraes Passos, 556', 'Sao Luis-MA', '65025-450', 'Brazil'),
(111, 'Charles Hoover', 'Male', 'Al. Tysiaclecia 98', 'Warszawa', '03-851', 'Poland'),
(112, 'Becky Moss', 'Female', 'Laivurinkatu 6', 'MIKKELI', '50120', 'Finland'),
(113, 'Frank Kidd', 'Male', '2491 Carson Street', 'Cincinnati', 'KY 45202', 'United States'),
(114, 'Donna Wilson', 'Female', 'Hallesches Ufer 69', 'Dettingen', '73265', 'Germany'),
(115, 'Lillian Roberson', 'Female', '36 Iolaire Road', 'NEW BARN', 'DA3 3FT', 'United Kingdom'),
(144, 'Stephen M. Menzies', 'Male', '577 Hartway Street', 'Bruie', '57325', 'United States'),
(143, 'Nikki G. Pascual', 'Female', '4291 Kinney Street', 'Agawam', '1001', 'United States'),
(141, 'Alpha A. Brookover', 'Female', '3542 Trainer Avenue', 'Kilbourne', '62655', 'United States'),
(142, 'Austin D. Salem', 'Male', '1184 Farland Street', 'Brockton', '2401', 'United States'),
(140, 'Bianca A. Carone', 'Female', '1777 Elkview Drive', 'Hialeah', '33012', 'United States'),
(139, 'Stephen M. Menzies', 'Male', '577 Hartway Street', 'Bruie', '57325', 'United States'),
(138, 'Nikki G. Pascual', 'Female', '4291 Kinney Street', 'Agawam', '1001', 'United States'),
(136, 'Alpha A. Brookover', 'Female', '3542 Trainer Avenue', 'Kilbourne', '62655', 'United States'),
(137, 'Austin D. Salem', 'Male', '1184 Farland Street', 'Brockton', '2401', 'United States'),
(145, 'Bianca A. Carone', 'Female', '1777 Elkview Drive', 'Hialeah', '33012', 'United States');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `full_text_searches`
--
ALTER TABLE `full_text_searches`
  ADD PRIMARY KEY (`CustomerID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `full_text_searches`
--
ALTER TABLE `full_text_searches`
  MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=146;


Now we want to make database connection in Laravel 6 framework. For this we have to open .env and in this file we have to define Mysql Database configuration.

.env

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


If you have perform full text search operation, then you will get SQL 1055 error. For prevent from this error, we have to open config/database.php file and in this file we have to change 'strict' => true, to 'strict' => false,. By change this configuration you can prevent from SQL 1055 error.


<?php

----

 'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

-----

?>




Create Model


Here we have use Model class for builder database query, so for this we have to create model class, so we have go to command prompt and write following command.


php artisan make:model Full_text_search --migration


This command will make model class in app/Full_text_search.php folder. In this file we have to add SearchableTrait of searchable package. After this we have to define table column name in which data we want to perform full text search, which you can seen below.

app/Full_text_search.php

<?php

namespace App;

use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Illuminate\Database\Eloquent\Model;
use Nicolaslopezj\Searchable\SearchableTrait;

class Full_text_search extends Model
{
    use Notifiable;
    use SearchableTrait;

    protected $searchable = [
        'columns' => [
            'full_text_searches.CustomerName'  => 10,
            'full_text_searches.Gender'   => 10,
            'full_text_searches.Address'   => 10,
            'full_text_searches.City'    => 10,
            'full_text_searches.PostalCode'  => 10,
            'full_text_searches.Country'   => 10,
            'full_text_searches.id'    => 10,
        ]
    ];

    protected $fillable = [
        'CustomerName', 'Gender', 'Address', 'City', 'PostalCode', 'Country',
    ];
}



Create Controller


For handle HTTP request we need to create controller in Laravel 6 application. For create controller here we have use compser command for this we have go to command prompt and run following command.


php artisan make:controller Full_text_search_Controller


This command will make controller file with name Full_text_search_Controller.php in app/Http/Controllers folder. In this class first we need to add model class by add code like use App\Full_text_search;. In this controller we have make following method.

index() - This is the root method of this controller. This method has simple load full_text_search.blade.php file in browser.

action(Request $request) - This method will received Ajax request for full text search data in mysql database. First this method has check incoming request is Ajax or not. If it is Ajax request then only it will execute rest of the code. If this request is Ajax then it will execute if block and by using full text search model with search package, it will perform full text search operation based on search query. After getting data it will return back data to Ajax request in json format.

app/Http/Controllers/Full_text_search_Controller.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Full_text_search;
use DataTables;

class Full_text_search_Controller extends Controller
{
    function index()
    {
     return view('full_text_search');
    }

    function action(Request $request)
    {
     if($request->ajax())
     {
      $data = Full_text_search::search($request->get('full_text_search_query'))->get();

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

    function normal_search(Request $request)
    {
        if($request->ajax())
        {
            $data = Full_text_search::latest()->get();
            return Datatables::of($data)->make(true);
        }
      
        return view('normal_search');
    }
}



Create View Blade File


For display output in browser, we need to create blade file in resources/views folder. Here we have create full_text_search.blade.php file in this folder. In this file first we have to create on search box for search data and then after we have create one table. After this we have to write Ajax request, which will send ajax request to action() method of controller and it will received full text search data in json format and convert into HTML table and display on web page using jQuery. This Ajax request will be trigger when we have click on search button. Whole source code of this file you can get below.

resources/views/full_text_search.blade.php

<html>
 <head>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Full Text Search in Laravel 6 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://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
 </head>
 <body>
  <div class="container">    
     <br />
     <h3 align="center">Full Text Search in Laravel 6 using Ajax</h3>
     <br />
     <div class="row">
      <div class="col-md-10">
       <input type="text" name="full_text_search" id="full_text_search" class="form-control" placeholder="Search" value="">
      </div>
      <div class="col-md-2">
       @csrf
       <button type="button" name="search" id="search" class="btn btn-success">Search</button>
      </div>
     </div>
     <br />
     <div class="table-responsive">
    <table class="table table-bordered table-striped">
        <thead>
         <tr>
            <th>Customer Name</th>
            <th>Gender</th>
            <th>Address</th>
                  <th>City</th>
                  <th>Postal Code</th>
                  <th>Country</th>
         </tr>
     </thead>
     <tbody></tbody>
    </table>
   </div>
        </div>
 </body>
</html>


<script>
$(document).ready(function(){

 load_data('');

 function load_data(full_text_search_query = '')
 {
  var _token = $("input[name=_token]").val();
  $.ajax({
   url:"{{ route('full-text-search.action') }}",
   method:"POST",
   data:{full_text_search_query:full_text_search_query, _token:_token},
   dataType:"json",
   success:function(data)
   {
    var output = '';
    if(data.length > 0)
    {
     for(var count = 0; count < data.length; count++)
     {
      output += '<tr>';
      output += '<td>'+data[count].CustomerName+'</td>';
      output += '<td>'+data[count].Gender+'</td>';
      output += '<td>'+data[count].Address+'</td>';
      output += '<td>'+data[count].City+'</td>';
      output += '<td>'+data[count].PostalCode+'</td>';
      output += '<td>'+data[count].Country+'</td>';
      output += '</tr>';
     }
    }
    else
    {
     output += '<tr>';
     output += '<td colspan="6">No Data Found</td>';
     output += '</tr>';
    }
    $('tbody').html(output);
   }
  });
 }

 $('#search').click(function(){
  var full_text_search_query = $('#full_text_search').val();
  load_data(full_text_search_query);
 });

});
</script>






resources/views/normal_search.blade.php// This file is for only get understanding how simple search is work and how Full text search is work.

<html>
 <head>
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Normal Search</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">Normal Search in Datatables</h3>
     <br />
   <div class="table-responsive">
    <table class="table table-bordered table-striped" id="user_table">
           <thead>
            <tr>
                <th>Customer Name</th>
                    <th>Gender</th>
                    <th>Address</th>
                    <th>City</th>
                    <th>Postal Code</th>
                    <th>Country</th>
            </tr>
           </thead>
       </table>
   </div>
   <br />
   <br />
  </div>
 </body>
</html>


<script>
$(document).ready(function(){

 $('#user_table').DataTable({
  processing: true,
  serverSide: true,
  ajax:{
   url: "{{ route('full-text-search.normal-search') }}",
  },
  columns:[
   {
    data: 'CustomerName',
    name: 'CustomerName'
   },
   {
    data: 'Gender',
    name: 'Gender'
   },
   {
        data: 'Address',
        name: 'Address'
      },
      {
        data: 'City',
        name: 'City'
      },
      {
        data: 'PostalCode',
        name: 'PostalCode'
      },
      {
        data: 'Country',
        name: 'Country'
      }
  ]
 });

});
</script>




Set Controller Method Route


In Laravel 6 application, we need to set the route of all controller method, for this we have to open routes/web.php file and in this file we have define route which you can find below.


<?php

Route::get('full-text-search', 'Full_text_search_Controller@index');

Route::post('full-text-search/action', 'Full_text_search_Controller@action')->name('full-text-search.action');

Route::get('full-text-search/normal-search', 'Full_text_search_Controller@normal_search')->name('full-text-search.normal-search');

?>


Run Laravel 6 Application


After all code is ready, lastly we need to start Laravel 6 framework server. For this we have to go command prompt and write following command.


php artisan serve


This command will start server and give you base url of your application. For test above code you have write following url in your browser.


http://127.0.0.1:8000/full-text-search


So, this our complete step by step Laravel 6 tutorial on how to make full text search application by using "nicolaslopezj/searchable" package with Ajax. If you want to download source code in zip file, so by click on below link you can download this tutorial source code file in zip folder.





3 comments:

  1. i got empty table in both! Also this error in normal search:"DataTables warning: table id=user_table - Ajax error. For more information about this error, please see http://datatables.net/tn/7"

    ReplyDelete
  2. Can i used full text search functionality in my live search in laravel and AJAX (https://www.cloudways.com/blog/live-search-laravel-ajax/ )? I have created the live search but now want to add auto complete functionality.

    ReplyDelete
  3. Thanks for the tutorial, it gave me an understanding in implementing AJAX live search into my laravel project. :)

    ReplyDelete