How to Integrate live search in PHP and MySQL with jQuery
I have received many requests from my readers for live search in PHP and MySQL with jQuery, so today i have decided to write a tutorial how to integrate live search in PHP and MySQL with jQuery. Searching is one of the most required feature in web sites and if it will be live searching it will show you fast result on page. In this tutorial we will create a database and a table insert records and start searching in it.
Database Details:
database name => phpgang
table name => live_search
column names => id, name, email
db.sql
Database file run in your mysql to create database and add data in table.
create database `phpgang`; use `phpgang`; CREATE TABLE `live_search` ( `id` int(10) NOT NULL auto_increment, `name` varchar(255) NOT NULL, `email` varchar(100) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; INSERT INTO `live_search` VALUES (1, 'Huzoor Bux', '[email protected]', '2013-08-29 11:04:36'); INSERT INTO `live_search` VALUES (2, 'Sameer Khan', '[email protected]', '2013-08-29 11:04:36'); INSERT INTO `live_search` VALUES (3, 'Ravi Khana', '[email protected]', '2013-08-29 11:05:31'); INSERT INTO `live_search` VALUES (4, 'Neelam Ara', '[email protected]', '2013-08-29 11:05:31');
db.php
Database configuration file edit database name, user and password as per your configuration.
<?php
$connection = mysqli_connect('localhost','phpgang_usr','******','phpgang');
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>index.php
Your index file where you have to add text box and include js and css.
<div class="content"> <input type="text" class="search" id="searchid" placeholder="Search for people" /> Ex: <b><i>huzoor bux, neelam, ravi or sameer</i></b><br /> <div id="result"></div> </div>
JavaScript
Here is the javascript you need to add in you file which can show you data on key press.
<script type="text/javascript" src="jquery-1.8.0.min.js"></script>
<script type="text/javascript">
$(function(){
$(".search").keyup(function()
{
var searchid = $(this).val();
var dataString = \'search=\'+ searchid;
if(searchid!=\'\')
{
$.ajax({
type: "POST",
url: "result.php",
data: dataString,
cache: false,
success: function(html)
{
$("#result").html(html).show();
}
});
}return false;
});
jQuery("#result").on("click",function(e){
var $clicked = $(e.target);
var $name = $clicked.find(\'.name\').html();
var decoded = $("<div/>").html($name).text();
$(\'#searchid\').val(decoded);
});
jQuery(document).live("click", function(e) {
var $clicked = $(e.target);
if (! $clicked.hasClass("search")){
jQuery("#result").fadeOut();
}
});
$(\'#searchid\').click(function(){
jQuery("#result").fadeIn();
});
});
</script>CSS
CSS i have used in this tutorial to show result well decorated as below.
<style type="text/css">
.content{
width:900px;
margin:0 auto;
}
#searchid
{
width:500px;
border:solid 1px #000;
padding:10px;
font-size:14px;
}
#result
{
position:absolute;
width:500px;
padding:10px;
display:none;
margin-top:-1px;
border-top:0px;
overflow:hidden;
border:1px #CCC solid;
background-color: white;
}
.show
{
padding:10px;
border-bottom:1px #999 dashed;
font-size:15px;
height:50px;
}
.show:hover
{
background:#4c66a4;
color:#FFF;
cursor:pointer;
}
</style>Last file you have to create to fetch results from database.
result.php
I have used direct mysql queries to fetch records but you can also use mysqli or PDO as mysql_connect deprecated in PHP 5.5.
<?php
include('db.php');
if($_POST)
{
$q = mysqli_real_escape_string($connection,$_POST['search']);
$strSQL_Result = mysqli_query($connection,"select id,name,email from live_search where name like '%$q%' or email like '%$q%' order by id LIMIT 5");
while($row=mysqli_fetch_array($strSQL_Result))
{
$username = $row['name'];
$email = $row['email'];
$b_username = '<strong>'.$q.'</strong>';
$b_email = '<strong>'.$q.'</strong>';
$final_username = str_ireplace($q, $b_username, $username);
$final_email = str_ireplace($q, $b_email, $email);
?>
<div class="show" align="left">
<img src="https://fbcdn-sphotos-e-a.akamaihd.net/hphotos-ak-prn1/27301_312848892150607_553904419_n.jpg" style="width:50px; height:50px; float:left; margin-right:6px;" /><span class="name"><?php echo $final_username; ?></span> <br/><?php echo $final_email; ?><br/>
</div>
<?php
}
}
?>Please share your comments and feedback.
Tutorial Categories:
What if needed more than 1 input?
Hi, I want to direct url when I search? Please help me.
Edit this line and add anchor tag in result.php.
Trying to download the file. I’ve subscribed and clicked the link you emailed me but I still get:
Sorry no email found subscribe below
when trying to download the file.
Your other scripts are easy and pretty but not this one. Requires much work it doesn’t work on arrow up and down and also it should get selection in text box on enter
I hope for the next tutorial you should use secure code. like OOP or PDO.
when i click the picture or $final_username, nothing happens.
everything is fine only if i click everywhere else.
any ideas?
hello! how can i get primary key id of selected value from database using this method? thanks for your help. 😀
and again – simple tutorial which should help people, and such dumb mistakes?
type: “POST”, url: “search.php”….
but there’s no “search.php” file, because you called it…. result.php?
Sorry for that mistake, now its fixed.
when you click the bold part of the search query result it dosent select the query…any solution to this please?…..it works if there is no formatting ie around the $q in the javascript file
Workaround:
jQuery(“#result”).live(“click”,function(e){
var $clicked = $(e.target);
if (e.target.nodeName == “STRONG”)
$clicked = $(e.target).parent().parent();
else if (e.target.nodeName == “SPAN”)
$clicked = $(e.target).parent();
var $name = $clicked.find(‘.name’).html();
var decoded = $(“”).html($name).text();
$(‘#searchid’).val(decoded);
});
i can’t download file plz give another link
Hi how can i get the id of the element that is clocked?
I do also have this problem:
“when i click the picture or $final_username, nothing happens.
everything is fine only if i click everywhere else.
any ideas?”
thanks for sharing this tutorial
it is working only if you add html.inc file….but if i remove this file it does not work
Please update the jquery code
Here you go…
$(function(){
$(“.search”).keyup(function()
{
var searchid = $(this).val();
var dataString = ‘search=’+ searchid;
if(searchid!=”)
{
$.ajax({
type: “POST”,
url: “result.php”,
data: dataString,
cache: false,
success: function(html)
{
$(“#result”).html(html).show();
}
});
}return false;
});
jQuery(“#result”).live(“click”,function(e){
var $clicked = $(e.target);
var $name = $clicked.find(‘.name’).html();
var decoded = $(“”).html($name).text();
$(‘#searchid’).val(decoded);
});
jQuery(document).live(“click”, function(e) {
var $clicked = $(e.target);
if (! $clicked.hasClass(“search”)){
jQuery(“#result”).fadeOut();
}
});
$(‘#searchid’).click(function(){
jQuery(“#result”).fadeIn();
});
});
I got this error: Warning: mysql_fetch_assoc() expects parameter 1 to be resource, string given in /home/webrokercom/public_html/simulador/result.php on line 12
What is wrong, please?
Tks!
That error means there is likely an error in your query you need to add the following between the closing bracket and semicolon in the mysql_query line:
or die(mysql_error())
Thanks a lot. It is working fine but I want to display the candidate’s rest details from table after clicking on his name from the drop down list. How can I call the other script doing that and passing the selected candidate id to it?
Fixed JavaScript:
$(function(){
$(“.search”).keyup(function()
{
var searchid = $(this).val();
var dataString = ‘search=’+ searchid;
if(searchid!=”)
{
$.ajax({
type: “POST”,
url: “result.php”,
data: dataString,
cache: false,
success: function(html)
{
$(“#result”).html(html).show();
}
});
}return false;
});
jQuery(“#result”).live(“click”,function(e){
var $clicked = $(e.target);
var $name = $clicked.find(‘.name’).html();
var decoded = $(“”).html($name).text();
$(‘#searchid’).val(decoded);
});
jQuery(document).live(“click”, function(e) {
var $clicked = $(e.target);
if (! $clicked.hasClass(“search”)){
jQuery(“#result”).fadeOut();
}
});
$(‘#searchid’).click(function(){
jQuery(“#result”).fadeIn();
});
});
Thanks buddy for fixing, now it works 😉
Cheers! 😉
hellow what if i want to make the filted result to open in another pager where should i put my link???
sorry for my poor english but i hope you understand
Thank you for this tutorial, and thanks Pro for this fix!
I have a question: How do we make the result box to disappear when we click somewhere else OR press Esc button? Thanks!
the site example doesn’t even work there…
Its working perfect please check again.
let me know which browser you are using?
hi ! when click on image and text then it’s not display result in text box.. i display in image..pls give me code for this thank you for old code it’s nice
hi ! when click on image and text then it’s not display result in text box.. i display in image..pls give me code for this thank you for old code it’s nice
hi ! when click on image and text then it’s not display result in text box.. i display in image..pls give me code for this thank you for old code it’s nice
hi ! when click on image and text then it’s not display result in text box.. i display in image..pls give me code for this thank you for old code it’s nice
This is a really good tutorial. How ever have you ever thought about writing a tutorial were you break each major line down and explain what it’s doing? For those of us who read this to learn and not too steal your work for our site, it would be beneficial. And once word got out that you are doing that you would have a lot more readers.
Thanks Huzoor for share the code 😉
Hi Huzoor,
I have country and city database [cities more then 2 L]. My Ajax search is very slow i have used indexing as well. can you please give me any idea.
i want to use it in wordpress
Great tutorial!
If i have in database “HTC Desire” and i search for “htc” it will lowercase the term and brings “htc Desire”… but i want it to appear as it is in database “HTC Desire”. How can i do that?
And another thing. Now we select the result and then click on search … I want to be able to click directly on the results from search list. How can i do that?
Thanks
I am not able see clicked option in textbox
pdo?