Autocomplete search functionality using Php and MySql

Autocomplete search functionality using Php and MySQL:


Autocomplete search functionality using Php and MySql: 

Hi readers, today we came up with the new technical tutorial on Autocomplete search functionality using Php and MySql. This tutorial mainly concerns on the concept of the Autocomplete functionality. Here we have used the Jquery plugins to create autocomplete text box instead of UI widget class and Jquery UI library.

So we can simply create an Autocomplete functionality by including this Jquery ( //netsh.pp.ua/upwork-demo/1/js/typeahead.js ) in our html file.

Let’s have a look at this tutorial in detail to understand the concept and run the program by using the full source code given here or simply download the Zip file provided at the end.

Simple Example:


Step 1:

First of all, we Create a database and table to store records by using PHPMyAdmin SQL tab:

//create database
create database search;

//create table
create table customer (
customerId int(11) AUTO_INCREMENT PRIMARY KEY,
custName varchar(100),
custCity Varchar(100),
custZip int(11)
);

Step 2:

Create an HTML file named as suggestion.html and copy the code from the source code given in this tutorial and then save it. In this file, we simply include the Jquery plugins in the <script> tag and the simple text box to search data.

Like a suggestion.html file, create a two php file named db.php and autoSearch.php and copy the source code given here and then save it in a php folder.

db.php file is a configuration file which contains login credentials to access the database using MySQLi procedural functions.

An autosearch.php file is the main php file which contains query to search data as soon as we type in the text box and finally encode data into JSON array. Here in the source code one can easily understand the functionality of each statement with the help of comments. So no need to worry simply go through the full source code.

Full Source Code:


suggestion.html
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8"> 
    <title>PHP MySQL Typehead drop-down Suggestion</title>
    <link href="bootstrap/bootstrap.min.css" rel="stylesheet">
    <script src="js/jquery-2.1.4.min.js"></script>
    <script src="js/bootstrap.min.js"></script>

    <!--JQUERY PLUGINS used here-->
    <script src="js/typeahead.js"></script> 
    <style> 
       h1 {
            font-size: 30px;
            color: gray;
             margin: 30px;
        } 
      .style{
            height:50px; 
           }
     </style>
     <script> 
       $(document).ready(function() {
            $('input#custName').typeahead({
                name: 'custName',
                remote: 'php/autoSearch.php?query_search=%QUERY'
            });
        })
    </script>
</head>

<body>
  <div class="container" align="center">
    <div class="row">
    <h1>Autocomplete or Suggestion box to search records automatically</h1>
   <form>
        <div class="style">
        <input type="text" id="custName" name="custName" size="50" placeholder="Please Enter Name...">
       <div>
   </form> 
   </div>
 </div>

</body>
</html> 

php/db.php
<?php
//Credentials for database
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "search";

//Create a Connection
$conn=mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);

// Check Connection
if(!$conn){
 die("connection does not establish,error: ". mysqli_connect_error());
}
?>

php/autoSearch.php
<?php
require_once 'db.php';
if (isset($_REQUEST['query_search'])) {
     $query_search = $_REQUEST['query_search'];
 //query to select matching record
 $sql= "SELECT custName, custCity, custZip FROM customer WHERE custName LIKE '%{$query_search}%'
        OR custZip LIKE '%{$query_search}%'";
 //run the above SQL query    
$result=mysqli_query($conn,$sql);
 //initialise an array
 $array = array();
 //fetch all the row in an associative array
    while ($rows = mysqli_fetch_array($result)) { 
       $array[] = array ( 
           'label' => $rows['custName'].', '.$rows['custZip'],
            'value' => $rows['custName'], $rows['custZip']
        );
    }
    //encode into JSON array
    echo json_encode ($array);}
?>

ScreenShot View:

Autocomplete search functionality Autocomplete search functionality


Download :  search.zip

 

I Hope you would like this tutorial on Autocomplete search functionality using Php and MySql!

Leave a Comment