Sunday, April 1, 2018

Ajax Live Search With PHP and MySQL

Today, In this tutorial we are going to discuss how to create php ajax live search box using MySQL database. 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.
Creating a PHP Search,PHP AJAX Live Search, Ajax Live Search With PHP and MySQL, Live search in php using jquery ajax without reloading page, How To Integrate Live Search In PHP And MySQL With JQuery, Ajax Live Search - PHP Tutorial, Ajax Search Box in PHP and MySQL, mysql - Livesearch php and ajax, How To Create Ajax Search Using PHP Jquery and Mysql


We have implemented this ajax live search box demo using php pdo connection. When user enter any text in search box and click on search button, Then it will send the request to "controller.php" page, with the help of ajax call and display the realtime search result in "search.php" page. Check out our blog archive on the topic if you’re looking to learn about Ajax Search Box in PHP, MySQL and JQuery.

Learn how To Integrate Live Search In PHP And MySQL With JQuery : 

Lets see the complete example to create live ajax search box.



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 when user click on search button, 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"><a style="text-decoration:none;" href="'.$data['POSTLINK'].'">'.$data['POSTTITLE'].'</a> </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 and click on search box.

  1. JQuery script helps to detect entered text in search box, when user click on 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: 80%;
 
}
.search-result{
 border-bottom:solid 1px #BDC7D8;
 padding:10px;
 font-family:Times New Roman;
 font-size: 20px;color:blue; 
}
#display-button{
 position:relative;
 width:80px;
 height:38px;
 float:right;
 left:-55px;
 text-align:center;
 float:right;
 left:-55px;
 background-color:#4683ea; 
}

</style>
<script>
// on click search results...
$(document).on("click", "#display-button" , function() { 
     var value = $("#search-data").val(); 
  if (value.length != 0) {
  //alert(99933);
        searchData(value);
    } else {     
         $('#search-result-container').hide();
    }   
});

// This function helps to send the request to retrieve data from mysql database...
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 > How To Integrate Live Search In PHP And MySQL With JQuery : </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 id="display-button" style="">  <img style="padding:7px;" src="search.png"  />  </div> 
</div>

<div id="search-result-container" style="border:solid 1px #BDC7D8;display:none; ">
</div>


</div>

</body>

</html>

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

This is all about ajax live search box demo using php pdo connection. Thank you for reading this article, and if you have any problem, have a another better useful solution about this article, please write message in the comment section.

2 comments:

  1. through above code is it possible to search other columns of same table?

    ReplyDelete
    Replies
    1. Yes, just you need to modify the SQL query according to your need.

      Delete