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
hi.....i got all source code from above but its not worked for me..please help me
ReplyDeletego die...
Deleteok, now u are responsible
Deletejquery.js
ReplyDeletebootstrap.js
bootstrap.css
how i can get above Js and Css.....
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
Deletefor 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
Bro, if u are not aware of Whtz jQuery & Bootstrap files then u should not be here..
DeleteThis comment has been removed by the author.
ReplyDeleteHi,
ReplyDeleteafter showing search results how to enter in table to select row on keydown press from search field?
How to make search work with Cyrillic symbols?
ReplyDeleteЕсли у Вас база данных в 1251 то вторая строчка поможет
Delete$connect = mysqli_connect("localhost", "root", "", "triol5");
$connect->set_charset("utf8");
i want to make the list of all customers shown at the beginning and when make search it filtered
ReplyDeletewhat's the edit ?
how to limit result
ReplyDeleteone way to doing this by modifying this line
Delete$sql = "SELECT * FROM tbl_customer LIMIT 10 WHERE CustomerName LIKE '%".$_POST["search"]."%'";
LIMIT 10 , should only show the 10 relevant results
This comment has been removed by the author.
ReplyDeletethanks
ReplyDeletehow to display all result
ReplyDeleteHow can I display from another table in single search form ?
ReplyDeletehow can i make it not live but by submin button?
ReplyDeleteSir 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
ReplyDeleteUse if(isset($_POST['search']))
Deleteafter using if(isset($_POST['search'])) still getting an error .Please help if it was successful
Deletemy search is not filtering search results as type in the textbox.what could be the problem?
ReplyDeleteyou can help me with my project from peru
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteI came up with this
ReplyDeletejust add this line of code and you are done !!!!1
error_reporting(0);
$sql = "SELECT * FROM tbl_customer WHERE CustomerName LIKE '%".$_POST["search"]."%'";
Works great! How can the search be made case insensitive?
ReplyDeleteundefined index search on my fetch.php in query
ReplyDeleteFor those with the undefined error:
DeleteWhen looking closely, a POST is being made, but you should use the GET for the search, that will solve all of your issues :)
thanks for providing this information. you may also refer-http://www.s4techno.com/blog/2016/07/17/mysql-connection-command/
ReplyDeleteHOW CAN I DO THAT WITH JSP?
ReplyDeleteHi, thanks for the tutorial!! I have a question, how can export the results to an excel file?
ReplyDeleteHow can I modify the JS to display results(call fetch) by default on page load unless search is used ?
ReplyDeleteI 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!!!!!
ReplyDeleteThumbs up.
DeleteVery true.
DeleteThanks 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.
ReplyDeleteWebsite for school uk
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
ReplyDeleteHow to use this in PDO ?
ReplyDeletehow is it possible to add pagination ?
ReplyDeleteis it possible to add pagination to it ?
ReplyDelete'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'
ReplyDeletethat's my error when I try to query the input text,what can i do to solve it?
please provide me with an answer.
very nicely explained ..thanks
ReplyDeleteScript 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?
ReplyDeleteScript 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?
ReplyDeletenice tutorial; awesome!!!!!
ReplyDeleteBut how to select fetched data
Well done! Thank you!!!!!
ReplyDeletethnaks so so much <3
ReplyDeleteThis works fast and easy but for some reason the results are duplicated a bunch of times, they arent duplicated in my database at all.
ReplyDeleteHelp, 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)
ReplyDeleteThanks Im just having difficulty Hon adding pagination on the result. Do you have idea how to do that?
ReplyDeleteThank you for this . Do you have a tutorial How to add a pagination?
ReplyDeletemysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\PMS\fetch.php on line 32
ReplyDeletekeep getting text undefined on in my if statement
ReplyDeletehi my table list is display but search not working. please help
ReplyDeleteSir i pls can you comment-system like this own your blog sir
ReplyDeletecan someone help me with this error?
ReplyDeleteWarning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\search\fetch.php on line 29
Can this also being made for search in local Excel file?
ReplyDeleteCan this also works to search in a local excel file?
ReplyDeleteplz help delete update link not working in this code
ReplyDeletenice tutorial
ReplyDeletehow to insert background
ReplyDeleteAwesome work. Thanks
ReplyDeleteHi!Please !who can help me to get php pdo version to fetch?
ReplyDeleteGreat one! Thank you it helped me.
ReplyDeleteI just want to say thank you for sharing this post, it was really awesome and very informative. Thank you.
ReplyDeleteIf someone looking for PHP training certification in India. Join us
The blog is so interactive and Informative , you should write more blogs like this Ruby on rails Online Training
ReplyDeleteThanks:)
ReplyDeleteHi! I have 1 million rows of data on my database, my browser crashed after a search. Can you help me regarding to this?
ReplyDelete1. 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!
what is wrong sir if the search bar will not function
ReplyDeleteits not work what's wrong, i can't fetch the data
ReplyDeletehi,
ReplyDeletecan you help me with an advice how can get one result of search and display it in another page (like product_details.php)?
hola que tal me sale esto error ...
ReplyDeletenull Data Not Found
me sale este error..
ReplyDeletenull Data Not Found
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
ReplyDeleteWarning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\Smartec\fetch.php on line 24
ReplyDeleteData Not Found
help
Thank u SO MUCH... really HELPP
ReplyDeleteWarning: mysqli_num_rows() expects parameter 1 to be mysqli_result, bool given in /Applications/XAMPP/xamppfiles/htdocs/ajax/fetch.php on line 29
ReplyDeleteData Not Found
this code works fine. but if i record words in Russian alphabet in my database, not working
ReplyDeleteEffective as a method ! Thank you so much
ReplyDeletethanks for this tutorial
ReplyDeletethank you so much...i wasted one full day..
ReplyDelete
ReplyDeleteI 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
Worked first time!! Unbelievably grateful. Highly recommended.
ReplyDeleteZindagi zhand hai phir bhi ghamand hai.
ReplyDeletei 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
ReplyDeletehi i want to ASK SOMETHING. how to add edit, delete and update button beside of COUNTRY column?
ReplyDeleteError: 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
ReplyDeleteThanks 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.
can any one help me wot create this search using MSSQL database please?
ReplyDeleteI want to make a live search using MSSQL database. please help
ReplyDeletecould you pls. share code for two search window.
ReplyDeleteDear Sir please make tutorial of PHP Ajax Live Search with Multiple Value and pagination
ReplyDeleteIt's really amazing code.Dear Sir! thanks for sharing.Very nicely explain. It's easy to understand. Once again thanks.
ReplyDeleteThanks a bunch! Solved my issue easily.
ReplyDeleteThanks it works well
ReplyDeleteIt 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.
ReplyDeleteI 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.
Thank for the tutorial
ReplyDeleteJust wondering how to use the same search with multi words (from different column) with space