October 1, 2020

Laravel: Live Search from Database with JSON Response using Query Builder

Live Search is a great way to retrieve specific rows from any database based upon user requirements. This in turn lowers the network overhead and makes your applications faster. In any large scale applications with humongous data-sets it is imperative that live search is used instead of displaying the entire table in one go.

In this tutorial you will learn to:-
  1. Live Search from Database
  2. Receive JSON Response
  3. Parsing JSON Response
  4. Adding and Removing <option> Dynamically in <select>
  5. Displaying Selected Option Data in a Table

This tutorial assumes that you already have a Laravel Environment Setup. I will be using the setup I have built in my earlier tutorial “A guide to Laravel v6 Queue with data extraction from MySQL” . The source code of both the tutorials is available at the end.

A Quick Recap: The table I will be using here is students table. In case you are in a hurry; here is the code for the table 🙂
--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `roll` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `result` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Step 1: Make a Live Search Controller

php artisan make:controller liveSearchContoller
Note: I made a spelling mistake here and named it liveSearchContoller instead of liveSearchController . But, don’t worry it won’t affect the tutorial

Step 2: Navigate to liveSearchContoller.php located inside app/Http/Controllers. The contents of the file should look like this.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class liveSearchContoller extends Controller
{
    //
}

Step 3: Add header use DB;’ to the file so that we can query from Database.

use DB;

Step 4: Write a new function liveStudentSearch() inside the liveSearchContoller.php

    public function liveStudentSearch(Request $request)
    { 

        $search_text = $request->search_text;
        $results = DB::table('students')
                  ->where('name', 'LIKE', $search_text . '%')
                  ->orWhere('roll','LIKE',$search_text . '%')
                  ->get();
        return response()->json([                      
                    'results' => $results
                  ]);
    }

We used query builder for the search query. Here, a search text is fed as input via post request. The query selects all rows from the database where either name or roll matches the search text. The Wildcard: “%” is appended after search text; It basically means that any string matching the search text exactly or any string starting with the search text followed by any number of characters.

Finally, the liveSearchContoller.php file will look like this:-

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use DB;

class liveSearchContoller extends Controller
{
    public function liveStudentSearch(Request $request)
    { 

        $search_text = $request->search_text;
        $results = DB::table('students')
                  ->where('name', 'LIKE', $search_text . '%')
                  ->orWhere('roll','LIKE',$search_text . '%')
                  ->get();
        return response()->json([                      
                    'results' => $results
                  ]);
    }
}

Step 5: Create two new routes in web.php for the function.

Route::get('/liveSearchExample', function () {return view('students/liveSearchExample'); })->name('liveSearchExample');
Route::post('liveStudentSearch', '[email protected]')->name('liveStudentSearch');

Step 6: Create a new view blade file named liveSearchExample.blade.php The route for the blade is already written in Step 5

@extends('template')

@section('main')
<div class="row">
<div class="col-sm-12">
	<a class="btn btn-success" href="{{ url('/students') }}">Examples Home</a>
	<form>
		<div>
				<br/>
                <input type="text" id="search_text" class="form-control" name="search_text" placeholder="Enter Student Name or Roll" onchange="studentFinder()" required style="width:50%">
                 <select class="form-control" id="inputStudent1" onchange="studentDisplay()" name="inputStudent1" style="width: 50%;">
                </select>
        </div>  
	</form>
<br/>
<br/>
 <table style="width:50%">
  <tr>
    <th>Name</th>
    <th>Roll</th>
    <th>Result</th>
  </tr>
  <tr>
    <td id="name1"></td>
    <td id="roll1"></td>
    <td id="result1"></td>
  </tr>
  <tr>
  </tr>
</table> 
</div>
</div>
<script>

	function studentDisplay(){
    
    var temp_name=$("#inputStudent1 option:selected").attr('data-name');
    var temp_roll=$("#inputStudent1 option:selected").attr('data-roll');
    var temp_result=$("#inputStudent1 option:selected").attr('data-result');

    document.getElementById('name1').innerHTML=temp_name;
    document.getElementById('roll1').innerHTML=temp_roll;
    document.getElementById('result1').innerHTML=temp_result;
	}

    function studentFinder() {

      
        var search_text=document.getElementById("search_text").value;
    	document.getElementById('name1').innerHTML='';
    	document.getElementById('roll1').innerHTML='';
    	document.getElementById('result1').innerHTML='';
      
        $.ajax({
            method: 'post',
            url: "{!!route('liveStudentSearch')!!}",
            data: {
              "_token": "{{ csrf_token() }}",
                "search_text":search_text
            },
            complete: function (result) {
            console.log(result.responseJSON.results);
            var new_list=result.responseJSON.results;
            var selectElement = document.getElementById('inputStudent1');
            selectElement.innerHTML = '';

            var temp;
            for(i = 0; i < new_list.length; i++)
            {
              if(i==0){
              $('#inputStudent1').append('<option value="'+new_list[i].id+'" data-name="'+new_list[i].name+'" data-roll="'+new_list[i].roll+'" data-result="'+new_list[i].result+'" data-mobile="'+new_list[i].mobile+'">'+new_list[i].name+' '+new_list[i].roll+'</option>');
    		  document.getElementById('name1').innerHTML=new_list[i].name;
    		  document.getElementById('roll1').innerHTML=new_list[i].roll;
    		  document.getElementById('result1').innerHTML=new_list[i].result;
          	  }
              else
              $('#inputStudent1').append('<option value="'+new_list[i].id+'" data-name="'+new_list[i].name+'" data-roll="'+new_list[i].roll+'" data-result="'+new_list[i].result+'" data-mobile="'+new_list[i].mobile+'">'+new_list[i].name+' '+new_list[i].roll+'</option>');             
            }
              
            }
        })
    }//end of studentFinder()
</script>
@endsection

The function studentFinder() in our blade file sends post request to our liveStudentSearch function which is inside the controller and parses the JSON Response and finally lists the options inside <select>.

Function studentDisplay() simply displays the name, roll and name of the selected student from the list of options.

Example

Source code is available at https://github.com/sa1if3/laravel-techflow360/

Saifur Rahman

Saifur Rahman is a Full Stack Laravel Developer. Additionally, he has spent a significant amount of time to learn and research in the domain of the Internet of Things (IoT). He loves to share his work and contribute to helping fellow developers. Saifur also runs the following websites and services - Pingsms.in and Techmion.com

View all posts by Saifur Rahman →

Leave a Reply

Your email address will not be published. Required fields are marked *