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

Database Table

 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=12 ;  
 --  
 -- 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 Constitución 2222', 'México D.F.', '05021', 'Mexico'),  
 (3, 'Antonio Moreno', 'Mataderos 2312', 'México D.F.', '05023', 'Mexico'),  
 (4, 'Thomas Hardy', '120 Hanover Sq.', 'London', 'WA1 1DP', 'UK'),  
 (5, 'Christina Berglund', 'Berguvsvägen 8', 'Lule', 'S-958 22', 'Sweden'),  
 (6, 'Pirkko Koskitalo', 'Torikatu 38', 'Oulu', '90110', 'Finland'),  
 (7, 'Paula Parente', 'Rua do Mercado, 12', 'Resende', '08737-363', 'Brazil'),  
 (8, 'Karl Jablonski', '305 - 14th Ave. S. Suite 3B', 'Seattle', '98128', 'USA'),  
 (9, 'Matti Karttunen', 'Keskuskatu 45', 'Helsinki', '21240', 'Finland'),  
 (10, 'Zbyszek', 'ul. Filtrowa 68', 'Walla', '01-012', 'Poland'),  
 (11, 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');  

index.php

 <html>  
      <head>  
           <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />  
           <title>Webslesson Tutorial</title>  
           <script src="jquery.js"></script>  
           <script src="js/bootstrap.js"></script>  
           <link href="css/bootstrap.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 Name" class="form-control" />  
                     </div>  
                </div>  
                <br />  
                <div id="result"></div>  
           </div>  
      </body>  
 </html>  
 <script>  
 $(document).ready(function(){  
      $('#search_text').keyup(function(){  
           var txt = $(this).val();  
           if(txt != '')  
           {  
                $.ajax({  
                     url:"fetch.php",  
                     method:"post",  
                     data:{search:txt},  
                     dataType:"text",  
                     success:function(data)  
                     {  
                          $('#result').html(data);  
                     }  
                });  
           }  
           else  
           {  
                $('#result').html('');                 
           }  
      });  
 });  
 </script>  

fetch.php

 <?php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 $output = '';  
 $sql = "SELECT * FROM tbl_customer WHERE CustomerName LIKE '%".$_POST["search"]."%'";  
 $result = mysqli_query($connect, $sql);  
 if(mysqli_num_rows($result) > 0)  
 {  
      $output .= '<h4 align="center">Search Result</h4>';  
      $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';  
 }  
 ?>  

32 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