How to implement live search using JQuery PHP and MySQL

Live search is when you display results the moment when the user starts entering data in a search box. The live search helps the user to filter and know whether the item he is searching for is present in the search results

The live search also helps the user to select the item he is in need of without typing the whole search term.

The live search ensures that the user will not click the search button so that he can view the results or otherwise get the message no results found

In this article, we shall discuss how to implement live search in a website using bootstrap, jQuery, PHP, and MySQL. We are going to have a way when the user is keying in an item in the search box, it is checked in the MySQL database instantly without the need of clicking a button

We are going to show the implementation of live search in a webpage by using an example. The example that we shall use to show the implementation of live search is searching for a country in a given search box.

Create a table called countries in your database as follows


CREATE TABLE countries (
  id int(11) NOT NULL,
  country_name varchar(100) NOT NULL,
  status int(11) NOT NULL DEFAULT '1',
  createdon datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
)

Add data to the countries table. For this demonstration, we have added 10 countries to the table


INSERT INTO countries (id, country_name, status, createdon) VALUES (NULL, 'United States', '1', CURRENT_TIMESTAMP), (NULL, 'United Kingdom', '1', CURRENT_TIMESTAMP), (NULL, 'Spain', '1', CURRENT_TIMESTAMP), (NULL, 'France', '1', CURRENT_TIMESTAMP), (NULL, 'Brazil', '1', CURRENT_TIMESTAMP), (NULL, 'Canada', '1', CURRENT_TIMESTAMP), (NULL, 'Russia', '1', CURRENT_TIMESTAMP), (NULL, 'Algeria', '1', CURRENT_TIMESTAMP), (NULL, 'Nigeria', '1', CURRENT_TIMESTAMP), (NULL, 'Kenya', '1', CURRENT_TIMESTAMP);

Create a PHP or HTML file that will contain the form with the search box which will be used to key in the country name

                   
<div style="margin-top:10%" class="row">
        <div class="col-md-3"></div>
        <div class="col-md-6">
            <h3>Live Search Example</h3>
            <hr>
            <form class="form">
               <div class="search-box">
                   <label>Country Name</label>
                 <input type="text" class="form-control" autocomplete="off" placeholder="enter country name..." />
                  <div class="resultsection"></div>
                </div>
            </form>
        </div>
        <div class="col-md-3"></div>
    </div>

Note, for our case we have used bootstrap so that we can make our code simple in styling

Add jQuery link to initialize jQuery in this code in the footer


<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>

Add the key up function script which ensures that when any character is added to the search box is sent to the database

Once the we collect the value from the search box or input field, we send it to the PHP file that will connect with the database as a POST data


<script>
  $('.search-box input[type="text"]').on("keyup input", function(){
        /* Get input value on change */
        var countryname = $(this).val();
        var displayresult = $(this).siblings(".resultsection");
        if(countryname.length>1){
            $.ajax({
            url: 'countrysearch',
            data: {countryname:countryname},
            type: 'POST',
            dataType:'json',
            success: function(data) {
                displayresult.html(data);
            }
           
            });
        } else{
            displayresult.empty();
        }
    });
</script>

After the result is shown on a dropdown during search, we add the click function to the result such that it is filled to the input field


// fill the search box with the result on click
    $(document).on("click", ".resultsection p", function(){
        $(this).parents(".search-box").find('input[type="text"]').val($(this).text());
        $(this).parent(".resultsection").empty();
    });

The full HTML code and JavaScript code with ajax data submission is as follows


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Live Search using PHP,MySQL and Jquery</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<style>
    .resultsection{
        position: absolute;       
        z-index: 999;
        top: 100%;
        left: 0;
    }
    /* Formatting result items */
    .resultsection p{
        margin: 0;
        padding: 7px 10px;
        border: 1px solid #CCCCCC;
        border-top: none;
        cursor: pointer;
    }
    .resultsection p:hover{
        background: #f2f2f2;
    }
</style>
</head>
<body>
    <div style="margin-top:10%" class="row">
        <div class="col-md-3"></div>
        <div class="col-md-6">
            <h3>Live Search Example</h3>
            <hr>
            <form class="form">
               <div class="search-box">
                   <label>Country Name</label>
                 <input type="text" class="form-control" autocomplete="off" placeholder="enter country name..." />
                  <div class="resultsection"></div>
                </div>
            </form>
        </div>
        <div class="col-md-3"></div>
    </div>
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<script>
$(document).ready(function(){
    $('.search-box input[type="text"]').on("keyup input", function(){
        /* Get input value on change */
        var countryname = $(this).val();
        var displayresult = $(this).siblings(".resultsection");
        if(countryname.length>1){
            $.ajax({
            url: 'countrysearch',
            data: {countryname:countryname},
            type: 'POST',
            dataType:'json',
            success: function(data) {
                displayresult.html(data);
            }
           
            });
        } else{
            displayresult.empty();
        }
    });
   
    // fill the search box with the result on click
    $(document).on("click", ".resultsection p", function(){
        $(this).parents(".search-box").find('input[type="text"]').val($(this).text());
        $(this).parent(".resultsection").empty();
    });
});
</script>
</body>
</html>

Create a PHP file where once the value in search box is collected and submitted through ajax post method, it can be processed and return the result if it is found else it returns the no result found


<?php
header("Content-Type:application/json");
//database connection replace with your database values
$con = mysqli_connect("localhost", "database_user", "database_password", "database_name") or die(mysqli_error($con));
if(isset($_POST["countryname"])){
    $countryname=$_POST["countryname"];
    $query = mysqli_query($con,"SELECT * FROM countries WHERE country_name LIKE '%$countryname%'");
    if($query){
            // Check number of rows in the result
            if(mysqli_num_rows($query) > 0){
                while($rows = mysqli_fetch_assoc($query)){
                    echo json_encode("<p>" . $rows["country_name"] . "</p>");
                }
            } else{
                echo json_encode("<p>No result found</p>");
            }
    }
}
 
// close connection
mysqli_close($con);
?>