Friday, 11 March 2016

Ajax Live Data Search using Jquery PHP MySql



In this tutorial we are going to learn Ajax Live Search with PHP and MySql with Jquery. In most of the site we can see there is one search bar on the site and we can search content of that site. If you have use Facebook or twitter there is one amazing live search functionality available for search new friends or followers. In those site suppose we enter some text for find from those site then we can get instant result from site without page refresh. This functionality is done by Ajax with Jquery. With the help of Jquery we can use Ajax http dom function, with the help of this function it search data on server side and send back result to front end webpage without page refresh. This functionality will give amazing look to your site. I have provide source code with this post. I hope you can learn this things.







Source Code


index.php


<html>
 <head>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <title>Webslesson Tutorial</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" />
 </head>
 <body>
  <div class="container">
   <br />
   <h2 align="center">Ajax Live Data Search using Jquery PHP MySql</h2><br />
   <div class="form-group">
    <div class="input-group">
     <span class="input-group-addon">Search</span>
     <input type="text" name="search_text" id="search_text" placeholder="Search by Customer Details" class="form-control" />
    </div>
   </div>
   <br />
   <div id="result"></div>
  </div>
 </body>
</html>


<script>
$(document).ready(function(){

 load_data();

 function load_data(query)
 {
  $.ajax({
   url:"fetch.php",
   method:"POST",
   data:{query:query},
   success:function(data)
   {
    $('#result').html(data);
   }
  });
 }
 $('#search_text').keyup(function(){
  var search = $(this).val();
  if(search != '')
  {
   load_data(search);
  }
  else
  {
   load_data();
  }
 });
});
</script>

fetch.php


<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$output = '';
if(isset($_POST["query"]))
{
 $search = mysqli_real_escape_string($connect, $_POST["query"]);
 $query = "
  SELECT * FROM tbl_customer 
  WHERE CustomerName LIKE '%".$search."%'
  OR Address LIKE '%".$search."%' 
  OR City LIKE '%".$search."%' 
  OR PostalCode LIKE '%".$search."%' 
  OR Country LIKE '%".$search."%'
 ";
}
else
{
 $query = "
  SELECT * FROM tbl_customer ORDER BY CustomerID
 ";
}
$result = mysqli_query($connect, $query);
if(mysqli_num_rows($result) > 0)
{
 $output .= '
  <div class="table-responsive">
   <table class="table table bordered">
    <tr>
     <th>Customer Name</th>
     <th>Address</th>
     <th>City</th>
     <th>Postal Code</th>
     <th>Country</th>
    </tr>
 ';
 while($row = mysqli_fetch_array($result))
 {
  $output .= '
   <tr>
    <td>'.$row["CustomerName"].'</td>
    <td>'.$row["Address"].'</td>
    <td>'.$row["City"].'</td>
    <td>'.$row["PostalCode"].'</td>
    <td>'.$row["Country"].'</td>
   </tr>
  ';
 }
 echo $output;
}
else
{
 echo 'Data Not Found';
}

?>

--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_customer`
--

CREATE TABLE IF NOT EXISTS `tbl_customer` (
  `CustomerID` int(11) NOT NULL AUTO_INCREMENT,
  `CustomerName` varchar(250) NOT NULL,
  `Address` text NOT NULL,
  `City` varchar(250) NOT NULL,
  `PostalCode` varchar(30) NOT NULL,
  `Country` varchar(100) NOT NULL,
  PRIMARY KEY (`CustomerID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

--
-- Dumping data for table `tbl_customer`
--

INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Maria Anders', 'Obere Str. 57', 'Berlin', '12209', 'Germany'),
(2, 'Ana Trujillo', 'Avda. de la Construction 2222', 'Mexico D.F.', '5021', 'Mexico'),
(3, 'Antonio Moreno', 'Mataderos 2312', 'Mexico D.F.', '5023', 'Mexico'),
(4, 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),
(5, 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(6, 'Wolski Zbyszek', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),
(7, 'Matti Karttunen', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),
(8, 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA'),
(9, 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),
(10, 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', '90110', 'Finland'),
(11, 'Paul Henriot', '2, rue du Commerce', 'Reims', '51100', 'France'),
(12, 'Helvetius Nagy', '722 DaVinci Blvd.', 'Kirkland', '98034', 'USA'),
(13, 'Karin Josephs', 'Luisenstr. 48', 'Butte', '59801', 'USA');

34 comments:

  1. hi.....i got all source code from above but its not worked for me..please help me

    ReplyDelete
  2. jquery.js
    bootstrap.js
    bootstrap.css

    how i can get above Js and Css.....

    ReplyDelete
    Replies
    1. thats for the Jquery dude. just click the link and save to your folder https://cdnjs.cloudflare.com/ajax/libs/jquery/3.0.0-beta1/jquery.js

      for bootstrap
      http://getbootstrap.com/getting-started/#download

      and go to folder just add copy the css and js folder and pase it to your folder if you have created already just paste and replace ... whatever :D , hope this will help you dude . XD

      Delete
    2. Bro, if u are not aware of Whtz jQuery & Bootstrap files then u should not be here..

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi,
    after showing search results how to enter in table to select row on keydown press from search field?

    ReplyDelete
  5. How to make search work with Cyrillic symbols?

    ReplyDelete
    Replies
    1. Если у Вас база данных в 1251 то вторая строчка поможет

      $connect = mysqli_connect("localhost", "root", "", "triol5");
      $connect->set_charset("utf8");

      Delete
  6. i want to make the list of all customers shown at the beginning and when make search it filtered
    what's the edit ?

    ReplyDelete
  7. Replies
    1. one way to doing this by modifying this line
      $sql = "SELECT * FROM tbl_customer LIMIT 10 WHERE CustomerName LIKE '%".$_POST["search"]."%'";

      LIMIT 10 , should only show the 10 relevant results

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
  9. How can I display from another table in single search form ?

    ReplyDelete
  10. how can i make it not live but by submin button?

    ReplyDelete
  11. Sir thanks for this tutorial this is very helpful. Its work to me but i have a problem because for this Notice: Undefined index: search in C:\xampp\htdocs\searchajax\fetch.php on line 4. sir please help i need it for our thesis. Thanks

    ReplyDelete
    Replies
    1. Use if(isset($_POST['search']))

      Delete
    2. after using if(isset($_POST['search'])) still getting an error .Please help if it was successful

      Delete
  12. my search is not filtering search results as type in the textbox.what could be the problem?

    ReplyDelete
  13. you can help me with my project from peru

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. I came up with this

    just add this line of code and you are done !!!!1



    error_reporting(0);
    $sql = "SELECT * FROM tbl_customer WHERE CustomerName LIKE '%".$_POST["search"]."%'";

    ReplyDelete
  17. Works great! How can the search be made case insensitive?

    ReplyDelete
  18. undefined index search on my fetch.php in query

    ReplyDelete
    Replies
    1. For those with the undefined error:
      When looking closely, a POST is being made, but you should use the GET for the search, that will solve all of your issues :)

      Delete
  19. thanks for providing this information. you may also refer-http://www.s4techno.com/blog/2016/07/17/mysql-connection-command/

    ReplyDelete
  20. Hi, thanks for the tutorial!! I have a question, how can export the results to an excel file?

    ReplyDelete
  21. How can I modify the JS to display results(call fetch) by default on page load unless search is used ?

    ReplyDelete
  22. I don't understand the majority of you guy's questions. It's almost like you want a hand out. I mean seriously people i'm not experienced with bootstraps and all and I got this to work perfectly, first shot on my database, not even following half of the instructions and even have it showing all results. I know some of you may not be experienced or just not learning but damnit, "How do you do this, that, this and that?" Like this dumb question about showing another table on a single search..... SERIOUSLY!??? COMMON SENSE GUYS! HELL GOOGLE IT!!!!!

    ReplyDelete
  23. Thanks for the good words! Really appreciated. Great post. I’ve been commenting a lot on a few blogs recently, but I hadn’t thought about my approach until you brought it up.

    Website for school uk

    ReplyDelete