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');

You can also see - Live Data Search in Codeigniter using Ajax JQuery

98 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. how to limit result

    ReplyDelete
    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. HOW CAN I DO THAT WITH JSP?

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

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

    ReplyDelete
  23. 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
  24. 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
  25. its working but i need to display only what i search .. if its blank the result is blank too .. can i have an edit . TIA

    ReplyDelete
  26. how is it possible to add pagination ?

    ReplyDelete
  27. is it possible to add pagination to it ?

    ReplyDelete
  28. 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%elias%'' at line 4'
    that's my error when I try to query the input text,what can i do to solve it?
    please provide me with an answer.

    ReplyDelete
  29. very nicely explained ..thanks

    ReplyDelete
  30. Script is perfect, but I'm facing with an issue where I would like to add sort functionality on table output. When I have result either before typing query or after the query, shown result can't be sorted by column name. Is it possible to add that functionality?

    ReplyDelete
  31. Script is perfect, but I'm facing with an issue where I would like to add sort functionality on table output. When I have result either before typing query or after the query, shown result can't be sorted by column name. Is it possible to add that functionality?

    ReplyDelete
  32. nice tutorial; awesome!!!!!
    But how to select fetched data

    ReplyDelete
  33. This works fast and easy but for some reason the results are duplicated a bunch of times, they arent duplicated in my database at all.

    ReplyDelete
  34. Help, the search works great but it keeps showing duplicates, at random it will show between 3 and 20 duplicates of 1 item (and its not like that in my database)

    ReplyDelete
  35. Thanks Im just having difficulty Hon adding pagination on the result. Do you have idea how to do that?

    ReplyDelete
  36. Thank you for this . Do you have a tutorial How to add a pagination?

    ReplyDelete
  37. mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\PMS\fetch.php on line 32

    ReplyDelete
  38. keep getting text undefined on in my if statement

    ReplyDelete
  39. hi my table list is display but search not working. please help

    ReplyDelete
  40. Sir i pls can you comment-system like this own your blog sir

    ReplyDelete
  41. can someone help me with this error?
    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\search\fetch.php on line 29

    ReplyDelete
  42. Can this also being made for search in local Excel file?

    ReplyDelete
  43. Can this also works to search in a local excel file?

    ReplyDelete
  44. plz help delete update link not working in this code

    ReplyDelete
  45. Awesome work. Thanks

    ReplyDelete
  46. Hi!Please !who can help me to get php pdo version to fetch?

    ReplyDelete
  47. Great one! Thank you it helped me.

    ReplyDelete
  48. I just want to say thank you for sharing this post, it was really awesome and very informative. Thank you.
    If someone looking for PHP training certification in India. Join us

    ReplyDelete
  49. The blog is so interactive and Informative , you should write more blogs like this Ruby on rails Online Training

    ReplyDelete
  50. Hi! I have 1 million rows of data on my database, my browser crashed after a search. Can you help me regarding to this?

    1. Can we activate the submit search button so that it will not conduct a query while typing?

    2. Can we put a limit on the result table and add pagination so that if the table has a bunch of result, the entire data will not display in a single column or table?

    Here's my demo site: http://webdevtemp.com/HeavenSEO_TestSite/Demo/

    Try to input "First" on the search field.

    Thank you so much!

    ReplyDelete
  51. what is wrong sir if the search bar will not function

    ReplyDelete
  52. its not work what's wrong, i can't fetch the data

    ReplyDelete
  53. hi,
    can you help me with an advice how can get one result of search and display it in another page (like product_details.php)?

    ReplyDelete
  54. hola que tal me sale esto error ...
    null Data Not Found

    ReplyDelete
  55. me sale este error..
    null Data Not Found

    ReplyDelete
  56. how to hide the listing first becasue laoding 20K of rows is slow, how to make this search show only when type first 4 characters

    ReplyDelete
  57. Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\Smartec\fetch.php on line 24
    Data Not Found

    help

    ReplyDelete
  58. Thank u SO MUCH... really HELPP

    ReplyDelete
  59. Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, bool given in /Applications/XAMPP/xamppfiles/htdocs/ajax/fetch.php on line 29
    Data Not Found

    ReplyDelete
  60. this code works fine. but if i record words in Russian alphabet in my database, not working

    ReplyDelete
  61. Effective as a method ! Thank you so much

    ReplyDelete
  62. thank you so much...i wasted one full day..

    ReplyDelete

  63. I get an error in my query, can you help me

    query succeeds on the screen but gives error when searching

    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\kumas\fetch.php on line 22
    Data Not Found


    Thank you from now

    ReplyDelete
  64. Worked first time!! Unbelievably grateful. Highly recommended.

    ReplyDelete
  65. Zindagi zhand hai phir bhi ghamand hai.

    ReplyDelete
  66. i got it if i combined those two coding. but it happened that it will show double search table. why? if i separate it, it incorrect

    ReplyDelete
  67. hi i want to ASK SOMETHING. how to add edit, delete and update button beside of COUNTRY column?

    ReplyDelete
  68. Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''%p%' OR Category '%p%' OR price '%p%' OR location '%p%'' at line 1

    ReplyDelete

  69. Thanks for sharing this such informational blog. plz keep posting and have also a informational site where anyone can learn <a href="https://www.phptpoint.com/ajax-tutorial/>ajax programming</a> and other languages.

    ReplyDelete
  70. can any one help me wot create this search using MSSQL database please?

    ReplyDelete
  71. I want to make a live search using MSSQL database. please help

    ReplyDelete
  72. could you pls. share code for two search window.

    ReplyDelete
  73. Dear Sir please make tutorial of PHP Ajax Live Search with Multiple Value and pagination

    ReplyDelete
  74. It's really amazing code.Dear Sir! thanks for sharing.Very nicely explain. It's easy to understand. Once again thanks.

    ReplyDelete
  75. Thanks a bunch! Solved my issue easily.

    ReplyDelete
  76. It looks like there might be some issues with large amounts of data, and very similar keywords in searching, what happened to me it seems like in the backend it was triggering multiple fetch database queries, and all were running at the same time ( ex one searchingg for 123 one searching for 1234, one searhcing for 12345 (at some point i had 12 concurrent searches triggered by one simple typing operation - one workaround was to oaste the search term) another was to actually disable instant search and use it with the enter key instead.
    I think the elegant solution would be for javascript to wait like i dont know 1-2 seconds for a pause in typing, and then send the search to the database.

    ReplyDelete
  77. Thank for the tutorial
    Just wondering how to use the same search with multi words (from different column) with space

    ReplyDelete