Saturday, November 11, 2017

Ajax Search Box in PHP, MySQL and JQuery.

In this tutorial we are going to build AJAX search box using PHP. Now a days every website has integrated this kind of search feature. This search box populate the results in real time from MySQL database based on entered text in search box.

This Search box of HTML is completely controlled by JQuery function. On key-up event of input field (e.g. Search Box), This JQuery function send the request to "controller.php" page via. AJAX technique. This request find the best match from MySQL database and display the result in "search.php" page.

Build Live Search Box Using PHP, MySQL and AJAX skptricks

Simple Ajax Search Example


First Create "Post" Database Table
CREATE TABLE `post` (
  `POSTID` int(3) NOT NULL,
  `POSTTITLE` varchar(100) NOT NULL,
  `POSTDETAILS` varchar(10000) NOT NULL,
  `POSTLINK` varchar(100) NOT NULL
)

Once You have created above table, Then put the records in this table.

config.php
Consists of database configuration details to establish database connection.

<?php
/* DATABASE CONFIGURATION */
define('DB_SERVER', 'localhost');
define('DB_DATABASE', 'skptricksdemo');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');

?>

controller.php
Controller work is to control the flow of execution. Here on Key-Up, JQuery script send request to "controller.php" page and it will process the request and return response to be displayed.

<?php
include("DAO.php");
if(isset($_POST["search-data"])){
 
 $searchVal = trim($_POST["search-data"]);
 $dao = new DAO();
 echo $dao->searchData($searchVal);
}

?>

DAO.php
This DAO class helps to establish the database connection and populate/fetch the records from MySQL database on AJAX call using JQuery.

<?php
include("config.php");
class DAO{
public function dbConnect(){
$dbhost = DB_SERVER; // set the hostname
$dbname = DB_DATABASE ; // set the database name
$dbuser = DB_USERNAME ; // set the mysql username
$dbpass = DB_PASSWORD;  // set the mysql password
try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbConnection;
}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
}
public function searchData($searchVal){
try {
$dbConnection = $this->dbConnect();
$stmt = $dbConnection->prepare("SELECT * FROM `post` WHERE `POSTTITLE` like :searchVal");
$val = "%$searchVal%"; 
$stmt->bindParam(':searchVal', $val , PDO::PARAM_STR);   
$stmt->execute();
$Count = $stmt->rowCount(); 
//echo " Total Records Count : $Count .<br>" ;
$result ="" ;
if ($Count  > 0){
while($data=$stmt->fetch(PDO::FETCH_ASSOC)) {          
$result = $result .'<div class="search-result">'.$data['POSTTITLE'].'</div>';    
}
return $result ;
}
}
catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
} 
}
?>

search.php
This page consists of Jquery, AJAX, PHP and HTML code, which helps to fetch records form MySQL database when user enter any text in search box.

  1. JQuery script helps to detect entered text in search box
  2. AJAX Code helps to fetch records from database and display the result in "search.php" page without page refresh.
<html>
  <head>
    <script src="jquery-3.2.1.min.js">
    </script>
    <style>
      #search-data{
        padding: 10px;
        border: solid 1px #BDC7D8;
        margin-bottom: 20px;
        display: inline;
        width: 100%;
      }
      .search-result{
        border-bottom:solid 1px #BDC7D8;
        padding:10px;
        font-family:Times New Roman;
        font-size: 20px;
        color:blue;
      }
    </style>
    <script>
      $(document).ready(function() {
        $('#search-data').unbind().keyup(function(e) {
          var value = $(this).val();
          if (value.length>3) {
            //alert(99933);
            searchData(value);
          }
          else {
            $('#search-result-container').hide();
          }
        }
                                        );
      }
                       );
      function searchData(val){
        $('#search-result-container').show();
        $('#search-result-container').html('<div><img src="preloader.gif" width="50px;" height="50px"> <span style="font-size: 20px;">Please Wait...</span></div>');
        $.post('controller.php',{
          'search-data': val}
               , function(data){
          if(data != "")
            $('#search-result-container').html(data);
          else    
            $('#search-result-container').html("<div class='search-result'>No Result Found...</div>");
        }
              ).fail(function(xhr, ajaxOptions, thrownError) {
          //any errors?
          alert(thrownError);
          //alert with HTTP error
        }
                    );
      }
    </script>
  </head>
  <body>
    <div style="width: 700px;margin:40px auto;">
      <div id="search-box-container" >
        <label > Jquery search box using Ajax : 
        </label>
        <br>
        <br>
        <input  type="text" id="search-data" name="searchData" placeholder="Search By Post Title (word length should be greater than 3) ..." autocomplete="off" />
      </div>
      <div id="search-result-container" style="border:solid 1px #BDC7D8;display:none; ">
      </div>
    </div>
  </body>
</html>



If You want to perform click event in search result list, then you might need to follow below steps:

METHOD - 1 :
---------------------------------
$('body').delegate(".search-result", "click", function(e){
    alert($(this).text());
    // write you logic.....

});

METHOD - 2 :
------------------------------
$(document).on('click', '.search-result', function() {
alert($(this).text());
        // write you logic.....
});

Download Link :
--------------------------
https://github.com/skptricks/php-Tutorials/tree/master/Ajax%20Search%20Box%20in%20PHP-%20MySQL%20and%20JQuery



8 comments:

  1. this is good my brodher ı use this in my side thanks

    ReplyDelete
  2. Replies
    1. It is working perfectly. Refer the project available in Github repository : https://github.com/skptricks/php-Tutorials/tree/master/Ajax%20Search%20Box%20in%20PHP-%20MySQL%20and%20JQuery

      Delete
  3. It works great, but how can I do to select one of the results of the search as the value of the input text, thanks

    ReplyDelete
    Replies
    1. If You want to perform click event in search result list, then you might need to follow below steps:

      METHOD - 1 :
      ---------------------------------
      $('body').delegate(".search-result", "click", function(e){
      alert($(this).text());
      // write you logic.....

      });

      Delete
  4. I appreciate the work that went into creating this demonstration, How would I be able to see all the data associated with the search criteria (all data columns associated with the name).

    ReplyDelete
  5. How to add click event please help

    ReplyDelete