Auto complete search with Ajax, jQuery, PHP and MYSQL

by Salitha Chathuranga December 27, 2017 2 Comments


Today I came up with another tutorial based on jQuery and Ajax. Apart from them PHP and MYSQL also connected with this article. I'm going to create an autocomplete search box which displays available results in the database, matched with the word phrase we enter. It's real time process. As soon as we enter the word, web page will display the relevant results. Then we can select a particular result and search the details of that record. First I'll show you a short demo to understand what you will build end of this tutorial.


GitHub link for source code: AutoComplete-Search

Now we are going to start the tutorial. First create a project folder to store the files in localhost(htdocs or www folder). Create all the files according to this folder structure.



Here, dbcon, fetch, index and search are PHP files.
dbcon.php  =>  To build up database connection
fetch.php    =>  To get the data from database to be displayed as auto suggested results
index.php   =>  To load the project by default
search.php  =>  To get and display the details of a particular record
sql folder    =>  Contains the SQL file to create the database
pics folder  =>  Contains the pictures stored in the database

Import the SQL file to your phpmyadmin to create the database. Database name: ajax_crud
Database is based on details of some countries.
Then we have to create the DB connection. Open dbcon.php file and enter the following codes.

dbcon.php


<?php
//build the connection variable and assign database credentials
$conn = mysqli_connect('localhost','root','','ajax_search');
//if DB connection fails, get a error message
if(!$conn){
    die('Connection Failed'.mysqli_connect_error());
}
?>

Now we are connected to the database. Open the index.php file to include the code to display the project when it is loaded on localhost.

index.php


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
    <link href="https://fonts.googleapis.com/css?family=Roboto" rel="stylesheet">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <title>Autocomplete Search</title>
    <style type="text/css">
        body{
            font-family: 'Roboto', sans-serif;
            margin-top: 30px;
            background-image: url('pics/back.jpg');
        }
        
        h2 {
            text-align: center;
        }
        .col-lg-6 input, li {
            margin: 0 auto;
            width: 300px;
        }
        .col-lg-6 label {
            margin: 0 auto;
            margin-top: 30px;
            font-size: 20px;
        }
        ul li:hover {
            cursor: pointer;
            background-color: whitesmoke;
        }
        button {
            margin-top: 10px;
        }
    </style>
</head>
<body>
    
    <div class="col-lg-12">
        <div class="col-lg-3"></div>
        <div class="col-lg-6">
            <h2 style="margin-top: 100px;">Autocomplete Search Bar with</h2><h2>jQuery, Ajax , PHP and MYSQL</h2>
            <br>  
            <form action="search.php" method="POST">
                <center>
                    <label>Search a country by name</label>
                </center>
                <br><br>
                <input type="text" id="searchBox" name="searchBox" class="form-control" autocomplete="off" placeholder="Type name of a country" required="required">
                <div id="result">
                    
                </div>
                <center>
                    <button class="btn btn-primary">Search</button>
                </center>               
            </form>   
            <div class="success"></div>  

        </div> 
        <div class="col-lg-3"></div>    
    </div>  
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>    
</body>
</html>
All the explanations are included as comments in the file. You can get the idea of the code by reading those comments. The result of this jQuery code is shown by a short video clip, below the code. Watch it and get the idea.

Now we have come to the most important part of the tutorial. It's jQuery and Ajax code part!
First include the below code to see what will jQuery do for us.

    <!-- start jQuery function -->
    <script>
       //Load content of all functions after the page is loaded completely
        $(document).ready(function(){
            //jQuery function to get the keys entered by keyboard
            $('#searchBox').keyup(function(){   
                //Store the entered values in query variable
                var query = $('#searchBox').val(); 
                //Pass the data to the console 
                console.log(query);
            });
        });

    </script>
    <!-- end jQuery function -->


I think you can understand what a power jQuery is having! Rest of the function and Ajax call will be entered later. Now we should write the code for fetch.php file. It is the file to get the results for suggestions. We need to prepare a query connecting to the database.

fetch.php


<?php
// Build up DB connection including cofiguration file
require ("dbcon.php");
if(isset($_POST['search'])){
    // Assign the fetched country to a variable and within the HTML tags with Boottrap class; list-group-item
    $response = "<ul class='list-group'><li class='list-group-item'>No countries found</li></ul>";

    $q = mysqli_real_escape_string($conn,$_POST["q"]);
    // mysql query to fetch the countries
    $query = "SELECT country FROM countries WHERE country LIKE '$q%'"; 
    // execution of the query. Output is a boolean value
    $res = mysqli_query($conn, $query);
    // Check if there are results matched
    if(mysqli_num_rows($res)>0){
        // Start the styling for fetch country list
        $response = "<ul class='list-group'>";
        // Display fetched all countries matched with the entered phrase
        while($row = mysqli_fetch_assoc($res)){
           // Concatenate the results to the previously started list
            $response .= "<li class='list-group-item'>".$row['country']."</li>";
        }
        // End the styling for fetch country list
        $response .= "</ul>";

    }
    // Close results
    exit($response);
}
?>

Use of Ajax

Now we are ready to modify the jQuery function in index.php file. We have to link fetch.php and send and fetch data connecting to the server. So then, Ajax comes to the party! We should include a Ajax call in this function to get and display the suggested countries in the list below the input tag. Replace this code with the code wrote for previously started jQuery function.


        // start jQuery function to load the content of all functions after the page is loaded completely
        $(document).ready(function(){
            //jQuery function to get the keys entered by keyboard
            $('#searchBox').keyup(function(){   
                var query = $('#searchBox').val();
                //check whether the entered word phrase contains at least one character
                if(query.length>0){             
                    // start Ajax call to get the suggestions
                    $.ajax({
                        //Path for PHP file to fetch suggestion from DB
                        url: "fetch.php", 
                        //Fetching method       
                        method: "POST",
                        //Data send to the server to get the results
                        data: {
                           search : 1,             
                           q: query 
                        },
                        //If data fetched successfully from the server, execute this function
                        success:function(data){   
                            //Print the fetched suggestion results in the section wih ID - #result      
                            $('#result').html(data);    
                        },
                        //Type of data sent to the server
                        dataType: "text"                
                    });
                    // end Ajax call to get the suggestions
                }
            });
        });

Here we go! Now you can see the suggestions in a HTML list tag...I think you followed me.
But still we have a problem..What is it? Can you click on a particular country and select it? I know the answer. We can not do it. So we have to modify our script further. A jQuery click function should be added to complete the script. Then we can click on a result and it will be inserted to the input tag. Add the below code after the end of jQuery keyup function.

        // Results are included in <li> tags. When needed result is found it can be clicked using this function
        $(document).on('click', 'li', function(){  
            //Assign the selected value to a variable called country
            var country = $(this).text();   
            $('#searchBox').val(country);
            //Include the selected result to the HTML section with ID - result
            $('#result').html("");          
        });

Ok! Our jQuery and Ajax functions are ready now..If you followed me properly, now you have an auto complete search! You can click on a country record and select it also. I think the rest of this tutorial is just another PHP code only!

When a country name is selected and entered to the input tag, then the other details of the selected country can be received after clicking on search button. We are going to implement this part finally. Open the search.php file and add the below code.

search.php


<?php
// Build up DB connection including cofiguration file
require ("dbcon.php");
// Assign the search button to get data through post method
$search = $_POST["searchBox"];
// SQL query to featch the details of a country
$sql = "SELECT * FROM countries WHERE country LIKE '$search%'";
// execution of the query. Output a boolean value
$res = mysqli_query($conn, $sql);
// Take the number of rows of countries starting with the entered word phrase
$count = mysqli_num_rows($res);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
 <link href="https://fonts.googleapis.com/css?family=Roboto" rel="stylesheet">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <title>Search Results</title>
    <style type="text/css">
        body{
            font-family: 'Roboto', sans-serif;
            margin-top: 30px;
            background-image: url('pics/back.jpg');
        }
        table {
         margin-top: 100px;
         font-size: 20px;
        }
        table td {
         width: 200px;
        }
    </style>
</head>
<body>
    <div class="col-lg-12">
     <div class="col-lg-3"></div>
     <div class="col-lg-6">
   <table class="table table-bordered">
<?php
// Check if there are rows matched with the query
if($count>0)
{
    // Output the details within a table with Bootstrap styles
       while($data = mysqli_fetch_assoc($res)){
            echo '<tr>';
            echo "<td>".'Name'."</td>"."<td>".$data['country']."</td>"; 
            echo '</tr>';
            echo '<tr>';
            echo "<td>".'Currency'."</td>"."<td>".$data['currency']."</td>";
            echo '</tr>';
            echo '<tr>';
            echo "<td>".'<br>National Flag'."</td>"."<td id='flag_td'>"."<img src='";echo $data["flag"];echo "' "; echo "style='height: 80px; width:150px;'"; echo">"."</td>";
            echo "</tr>";
      echo '</table>';
 }
}
// If there are no matching countries this will displayed in the browser
else {
 echo "<center><h3 style='margin-top: 150px;'>No Data Matched with your input!</h3></center>";
}
?>
        <!-- Return button to the input page -->
        <center>
            <a href="index.php" class="btn btn-default">Back</a>
        </center>
    </div>
    </div>
      </body>
</html>

}

All the explanations are given in the code as comments. So now you can get the other details of the countries also from database.

My main target was to demonstrate you how to implement an auto complete search with Ajax and jQuery. So I think I did it. Try to understand the code and get the output! That's all in this article. I will meet you from another one..
Good Bye!




2 Comments