Sunday 19 January 2020

How to Create AutoComplete Textbox using PHP with jQuery Ajax



Do you know what is AutoSuggest or AutoComplete textbox? The AutoSuggest Textbox means when user has type in search textbox, then they can get quickly get result according what they has type in search box, and they can select value form pre populated search suggestion from the list. This AutoSuggestion feature will load suggestion automatically, when user has type some character into Search textbox field. Here we will make lightweight AutoSuggest or AutoComplete textbox by using PHP script with Mysql Database, jQuery and Ajax.

Now in your mind one question will arise, How it called light weight AutoSuggest Textbox. This is because here when user has type in search textbox, then it will only one time fetch data from Mysql database, and then after by using jQuery plugin it will filter data from data which has fetch from database and display filter result in AutoSuggest search result. So, Here we have called this light weight AutoSuggest or AutoComplete Textbox, because it is only send one request to Mysql database and fetch data and by using jQuery plugin it has filter result from that data and display as AutoSuggest result below Search box.

In Most of AutoComplete or AutoSuggest tutorial, we have seen on every character type, it has send request to Mysql database using Ajax and display search result, but In this tutorial, it will only send one Ajax request to Mysql database and then after data will be filter by using jQuery and display one web page. For make this tutorial, here we have use jQuery "JsLocalSearch" plugin, which is very light weight and fast jquery plugin, which used for filter records from client side data and which is mainly used for filter and search a of element which we have provide. It main feature is to search html content which we have provide. Below you can find source code of How to make AutoSuggest Textbox with Bootstrap library by using PHP script with jQuery Ajax and Mysql database.







Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `customer_table`
--

CREATE TABLE `customer_table` (
  `customer_id` int(11) NOT NULL,
  `customer_first_name` varchar(200) NOT NULL,
  `customer_last_name` varchar(200) NOT NULL,
  `customer_email` varchar(300) NOT NULL,
  `customer_gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customer_table`
--
ALTER TABLE `customer_table`
  ADD PRIMARY KEY (`customer_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customer_table`
--
ALTER TABLE `customer_table`
  MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT;





index.php



<!DOCTYPE html>
<html>
 <head>
  <title>jQuery Auto Suggest Textbox with Bootstrap 4 using PHP Ajax</title>
  <script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
  <script src="JsLocalSearch.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" />
  <style> 
  .mark {
    background-color: #d7ffe7 !important
  }

  .mark .gsearch{
    font-size: 20px
  }

  .unmark {
    background-color: #e8e8e8 !important
  }

  .unmark .gsearch{
    font-size: 10px
  }
  
  .marktext
  {
   font-weight:bold;
   background-color: antiquewhite;
  }
  </style>
 </head>
 <body>
  <br />
  <br />
  <div class="container">
   <h3 align="center">jQuery Auto Suggest Textbox with Bootstrap 4 using PHP Ajax</h3>
   <br />
   <br />
   <div class="row">
    <div class="col-md-3"></div>
    <div class="col-md-6">
     <input type="text" id="gsearchsimple" class="form-control input-lg" placeholder="Search..." />

     <ul class="list-group">

     </ul>
     <div id="localSearchSimple"></div>
     <div id="detail" style="margin-top:16px;"></div>
    </div>
    <div class="col-md-3"></div>
   </div>
  </div>
 </body>
</html>
<script>
$(document).ready(function(){
 $('#gsearchsimple').keyup(function(){
  var query = $('#gsearchsimple').val();
  $('#detail').html('');
  $('.list-group').css('display', 'block');
  if(query.length == 2)
  {
   $.ajax({
    url:"fetch.php",
    method:"POST",
    data:{query:query},
    success:function(data)
    {
     $('.list-group').html(data);
    }
   })
  }
  if(query.length == 0)
  {
   $('.list-group').css('display', 'none');
  }
 });

 $('#localSearchSimple').jsLocalSearch({
  action:"Show",
  html_search:true,
  mark_text:"marktext"
 });

 $(document).on('click', '.gsearch', function(){
  var email = $(this).text();
  $('#gsearchsimple').val(email);
  $('.list-group').css('display', 'none');
  $.ajax({
   url:"fetch.php",
   method:"POST",
   data:{email:email},
   success:function(data)
   {
    $('#detail').html(data);
   }
  })
 });
});
</script>


fetch.php



<?php

//fetch.php;

$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");

if(isset($_POST['query']))
{
 $query = "
 SELECT DISTINCT customer_email FROM customer_table 
 WHERE customer_email LIKE '%".trim($_POST["query"])."%'
 ";

 $statement = $connect->prepare($query);

 $statement->execute();

 $result = $statement->fetchAll();

 $output = '';

 foreach($result as $row)
 {
  $output .= '
  <li class="list-group-item contsearch">
   <a href="javascript:void(0)" class="gsearch" style="color:#333;text-decoration:none;">'.$row["customer_email"].'</a>
  </li>
  ';
 }

 echo $output;
}

if(isset($_POST['email']))
{
 $query = "
 SELECT * FROM customer_table 
 WHERE customer_email = '".trim($_POST["email"])."' 
 LIMIT 1
 ";

 $statement = $connect->prepare($query);

 $statement->execute();

 $result = $statement->fetchAll();

 $output = '
 <table class="table table-bordered table-striped">
  <tr>
   <th>First Name</th>
   <th>Last Name</th>
   <th>Email</th>
   <th>Gender</th>
  </tr>
 ';

 foreach($result as $row)
 {
  $output .= '
  <tr>
   <td>'.$row["customer_first_name"].'</td>
   <td>'.$row["customer_last_name"].'</td>
   <td>'.$row["customer_email"].'</td>
   <td>'.$row["customer_gender"].'</td>
  </tr>
  ';
 }
 $output .= '</table>';

 echo $output;
}

?>


So, If you have follow above source code, then you can learn How to Create Light Weight dynamic AutoSuggest or AutoComplete Textbox by using PHP script with Mysql database, jQuery and Ajax. If you want to get this tutorial source code file in zip folder, you can email us at webslesson@gmail.com.

8 comments:

  1. Thank you for your time FOR doing these courses ... THX U

    ReplyDelete
  2. Thank you for your time FOR doing these courses ... THX U

    ReplyDelete
  3. I have copied pasted the code but mark text and selecting the result is not working. Help me out this problem.

    ReplyDelete
    Replies
    1. add the JsLocalSearch plugin in to your parent folder

      Delete
  4. Thank you so much for ur help in creating this tutorial.It is very helpfull for a beginner like us...The code is working perfectly

    ReplyDelete
  5. Thank you, the code is working fine. Although i have this issue, my default database collation is set to utf8_ci general when i have a value like Girl's in that database and i search for the keyword girl... auto search shows values, when clicked on all other values gives back the results but not for strings that have the word Girl's with that special character symbol. Guys try this and help me how to solve it... use the same SQL

    SELECT * FROM products
    WHERE product_name = '".trim($_POST["product_name"])."'
    LIMIT 1

    ReplyDelete
  6. its very nice, but at present its not selecting from keyboard down arrow. Can you please also add keyboard down arrow & Enter key for selection. Then it will become more wonderful

    ReplyDelete
  7. how can i move the output table to the left of the screen

    ReplyDelete