June 20, 2013 9376 Views 4 Comments

Live Search with JQuery, Php and MySQL

SHARE THIS ARTICLE

Building a live search system using jQuery Ajax, PHP and MySQL. Unlike the traditional search box that reloads the page on submit, live search systems are able to return query results as you type.

Many users find real-time search systems highly intuitive and fun to use. Let’s look into how they work.

DEMO DOWNLOAD

chngpass_0

THE SEARCH FORM

This is actually a regular html search box, nothing fancy here.

<div class="search_widget">
<h3>Search for Meter:</h3>
<div class="clearfix"> </div>
<div class="srch">
<div id="srch-content">
<div class="search-content">
<div class="searchinputbox">
<!-- The Searchbox Starts Here -->
<input name="query" type="text" id="search_input" placeholder="Begin Typing to Search">
<!-- The Searchbox Ends Here -->
</div>
<div id="searchresultdata" class="srch-results"> </div>
</div>
</div>
</div>
</div>

THE QUERY MAGIC

Now, lets use JQuery to track input changes in the search field. On each change we’ll send a post to ajax-search.php

For the watermark in the search field, use the jQuery Watermark plugin.

$(document).ready(function() {
$("#search_input").watermark("Begin Typing to Search");
$("#search_input").keyup(function(){
var search_input = $(this).val();
var dataString = 'keyword='+ search_input;
if(search_input.length<=0){
$("#searchresultdata").remove();
}else{
if ( $("#searchresultdata").length ==0 ) {
$('<div id="searchresultdata" class="srch-results"></div>').appendTo('.srch-content');
}

//AJAX POST
$.ajax({
type: "POST",
url: "../ajax-search.php",
data: dataString,
beforeSend: function() {
$('input#search_input').addClass('loading');
},
success: function(response){
$('#searchresultdata').html(response).show();
if ($('input#search_input').hasClass("loading")) {
$("input#search_input").removeClass("loading");
}
}
});
}
});
});

ADDING CSS

Use css to position the elements, style the text and give the search box some visual appeal.

h3{
font-family: sans-serif, Verdana, Geneva, Arial, Helvetica;
font-size: 18pt;
color: #555;
padding: 0px;
margin: 0px;
border-bottom: 3px solid #555;
}

.search_widget{
height: 330px;
margin: 12px;
position: absolute;
top: 20px;
width: 527px;
z-index: 2;
overflow: auto
}

img {
border:0px;
}

.searchinputbox input {
font-size:16px;
color:#6e6e6e;
padding:10px;
border:none;
width:340px;
}

.searchinputbox input.loading {
background:url(loading_animate.gif) no-repeat right 50%;
}

.searchinputbox {
border:1px solid #ddd;
margin:0 20px;
width: 350px;
}
.searchinputbox input {
padding:10px 4px;
}

.srch-content p b {
color: #F6E19A;
font-size: 115%;
}

.srch-content p b{
color: #000000;
display: block;
font-family: "Lucida Grande","Lucida Sans Unicode",Arial,Helvetica,sans-serif;
font-size: 13px;
padding-bottom: 0.2em;
}

#searchresultdata{
margin:15px;
}

PROCESS THE JQUERY POST

In order to lookup the search key we must first establish a connection to the database. After doing so sterilize the POST and do a query, the results are then padded with html and sent back to the jQuery script. The jQuery script will then append the result to the interface.

//ESTABLISH DATABASE CONNECTION
define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASS", "");
define("DB_NAME", "myDB");

//Connect to mysql server
$link=@mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
if(!$link) {
print("Failed to establish connection to mysql server!");
exit();
}

if(isset($_POST['keyword'])){
$keyword = trim($_POST['keyword']);
$keyword = mysqli_real_escape_string($link, $keyword);
$query = "select meter_name,meter_location from meters where meter_name like '$keyword%'"; //MUST BEGIN WITH $KEYWORD

//echo $query;
$result = mysqli_query($link,$query);
if($result){
if(mysqli_affected_rows($link)!=0){
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
echo '<span><a class="meterRow">'.$row['meter_name'].'</a><span>'.$row['meter_location'].'</span></span>';
}
}else {
echo 'No Results for :"'.$_POST['keyword'].'"';
}
}
}else {
echo 'Parameter Missing';
}

DEMO DOWNLOAD

  • Sukey

    I love this. Live search is super useful on sites where there is a broad range of content. It helps the user, in real time, to know how specific his search has to be. Looking for that post on woo commerce? You could start with woo and see if that returned what you need, but if you’re getting hits related to wood and wool and wookies, you can keep typing until you’ve honed in on woo commerce without clicking the button and waiting for the reload. The example is appreciated.

    • Dennis

      I agree with Sukey. The live search functionality is very useful and makes the user feel like they are using a more modern website. Google instant search has made users want information they are looking for even faster and this would do the trick!

  • smus

    Very good example! However what if I need to expand it? I mean live-search by several parameters with checkboxes on the form and the search at the several mysql-fields? Could you please hint or provide an example code how it should be implemented?

    • kodesmart

      Hi smus.

      You should be able to append additional parameters to the sql query by adding AND|OR param2 like ‘$keyword2%’ AND param3 like ‘$keyword3%’.