Friday, 11 March 2016

Ajax Live Data Search using Jquery PHP MySql

           , , ,      98 comments   






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.




See Also





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. Unknown21 March 2016 at 00:42

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

    ReplyDelete
  2. Unknown21 March 2016 at 00:47

    jquery.js
    bootstrap.js
    bootstrap.css

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

    ReplyDelete
    Replies
    1. Unknown14 April 2016 at 10:17

      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. Unknown20 November 2016 at 08:12

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

      Delete
  3. Unknown4 April 2016 at 00:18

    This comment has been removed by the author.

    ReplyDelete
  4. Unknown4 April 2016 at 00:18

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

    ReplyDelete
  5. Unknown12 April 2016 at 04:33

    How to make search work with Cyrillic symbols?

    ReplyDelete
    Replies
    1. Unknown9 November 2016 at 05:30

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

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

      Delete
  6. Unknown25 May 2016 at 13:58

    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. Anonymous28 May 2016 at 09:15

    how to limit result

    ReplyDelete
    Replies
    1. Peter2 July 2016 at 07:14

      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. Peter2 July 2016 at 06:34

    This comment has been removed by the author.

    ReplyDelete
  9. Taksal Pri. School12 July 2016 at 14:28

    thanks

    ReplyDelete
  10. Unknown14 July 2016 at 19:02

    how to display all result

    ReplyDelete
  11. Administrator15 July 2016 at 03:11

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

    ReplyDelete
  12. sHAsHiLx5 August 2016 at 13:43

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

    ReplyDelete
  13. Unknown1 September 2016 at 08:30

    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. Unknown8 September 2016 at 23:25

      Use if(isset($_POST['search']))

      Delete
    2. The developer21 September 2016 at 02:01

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

      Delete
  14. The developer21 September 2016 at 01:48

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

    ReplyDelete
  15. Unknown3 October 2016 at 14:13

    you can help me with my project from peru

    ReplyDelete
  16. Unknown4 October 2016 at 14:16

    This comment has been removed by the author.

    ReplyDelete
  17. Unknown4 October 2016 at 14:20

    This comment has been removed by the author.

    ReplyDelete
  18. Unknown4 October 2016 at 14:25

    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
  19. steross1714 October 2016 at 09:06

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

    ReplyDelete
  20. Unknown20 October 2016 at 23:25

    undefined index search on my fetch.php in query

    ReplyDelete
    Replies
    1. Unknown10 November 2016 at 08:09

      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
  21. Unknown10 November 2016 at 22:45

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

    ReplyDelete
  22. Anonymous20 November 2016 at 14:43

    HOW CAN I DO THAT WITH JSP?

    ReplyDelete
  23. Unknown22 November 2016 at 04:32

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

    ReplyDelete
  24. Unknown6 December 2016 at 23:22

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

    ReplyDelete
  25. Jugalu17 December 2016 at 14:14

    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
  26. Anonymous5 January 2017 at 02:07

    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
  27. Unknown28 February 2017 at 12:43

    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
  28. Adhieresthenes Hier Banu Arfakhshad2 April 2017 at 20:37

    How to use this in PDO ?

    ReplyDelete
  29. mario2 June 2017 at 04:05

    how is it possible to add pagination ?

    ReplyDelete
  30. mario2 June 2017 at 04:05

    is it possible to add pagination to it ?

    ReplyDelete
  31. Elias4 July 2017 at 01:42

    '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
  32. Unknown11 August 2017 at 00:04

    very nicely explained ..thanks

    ReplyDelete
  33. Unknown19 August 2017 at 11:44

    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
  34. Unknown19 August 2017 at 11:44

    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
  35. raman24 August 2017 at 21:36

    nice tutorial; awesome!!!!!
    But how to select fetched data

    ReplyDelete
  36. Unknown5 October 2017 at 13:14

    Well done! Thank you!!!!!

    ReplyDelete
  37. Unknown14 October 2017 at 13:45

    thnaks so so much <3

    ReplyDelete
  38. Unknown15 October 2017 at 19:58

    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
  39. Unknown15 October 2017 at 20:00

    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
  40. Unknown15 October 2017 at 20:13

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

    ReplyDelete
  41. Unknown15 October 2017 at 20:25

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

    ReplyDelete
  42. Unknown6 November 2017 at 08:39

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

    ReplyDelete
  43. az5 December 2017 at 21:15

    keep getting text undefined on in my if statement

    ReplyDelete
  44. Unknown7 December 2017 at 19:26

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

    ReplyDelete
  45. Unknown14 January 2018 at 16:26

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

    ReplyDelete
  46. pipoy26 January 2018 at 20:56

    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
  47. GizmoS27 February 2018 at 02:07

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

    ReplyDelete
  48. GizmoS27 February 2018 at 02:07

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

    ReplyDelete
  49. Unknown9 March 2018 at 13:42

    plz help delete update link not working in this code

    ReplyDelete
  50. Unknown13 July 2018 at 01:46

    nice tutorial

    ReplyDelete
  51. Unknown26 July 2018 at 04:18

    how to insert background

    ReplyDelete
  52. Anonymous17 August 2018 at 09:25

    Awesome work. Thanks

    ReplyDelete
  53. Unknown30 August 2018 at 09:19

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

    ReplyDelete
  54. Unknown24 September 2018 at 13:07

    Great one! Thank you it helped me.

    ReplyDelete
  55. Unknown26 September 2018 at 00:43

    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
  56. Tejuteju1 October 2018 at 06:06

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

    ReplyDelete
  57. Unknown11 October 2018 at 10:56

    Thanks:)

    ReplyDelete
  58. Unknown11 October 2018 at 20:32

    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
  59. Unknown12 October 2018 at 09:20

    what is wrong sir if the search bar will not function

    ReplyDelete
  60. Unknown12 October 2018 at 09:23

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

    ReplyDelete
  61. Unknown30 October 2018 at 23:58

    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
  62. abad fores31 October 2018 at 10:59

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

    ReplyDelete
  63. abad fores31 October 2018 at 11:03

    me sale este error..
    null Data Not Found

    ReplyDelete
  64. Fan Of Snoopy7 December 2018 at 01:27

    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
  65. Unknown13 December 2018 at 12:14

    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
  66. Unknown27 December 2018 at 20:40

    Thank u SO MUCH... really HELPP

    ReplyDelete
  67. Aviansh HUge31 January 2019 at 05:51

    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
  68. Unknown22 February 2019 at 11:38

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

    ReplyDelete
  69. Unknown26 April 2019 at 08:35

    Effective as a method ! Thank you so much

    ReplyDelete
  70. Mofiz Uddin3 May 2019 at 23:24

    thanks for this tutorial

    ReplyDelete
  71. Superhero and Princess party13 June 2019 at 10:01

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

    ReplyDelete
  72. muhammetgl13 July 2019 at 12:36


    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
  73. Unknown4 October 2019 at 08:55

    Worked first time!! Unbelievably grateful. Highly recommended.

    ReplyDelete
  74. Abhay Tiwari5 November 2019 at 10:06

    Zindagi zhand hai phir bhi ghamand hai.

    ReplyDelete
  75. Munananaaaaa12 January 2020 at 19:50

    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
  76. PEACH 21 January 2020 at 00:11

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

    ReplyDelete
  77. Unknown20 April 2020 at 03:11

    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
  78. Phptpoint19 May 2020 at 00:30


    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
  79. Lavkush2 June 2020 at 10:18

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

    ReplyDelete
  80. Lavkush2 June 2020 at 10:19

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

    ReplyDelete
  81. Unknown19 June 2020 at 04:27

    could you pls. share code for two search window.

    ReplyDelete
  82. Unknown20 August 2020 at 01:23

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

    ReplyDelete
  83. Universal SEO25 September 2020 at 16:06

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

    ReplyDelete
  84. Unknown19 October 2020 at 23:21

    Thanks a bunch! Solved my issue easily.

    ReplyDelete
  85. Selva Arasu18 February 2021 at 02:36

    Thanks it works well

    ReplyDelete
  86. Infoscript4 March 2021 at 13:36

    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
  87. Mohamed14 December 2021 at 10:21

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

    ReplyDelete