Google like autosuggestion Search box using PHP, MySQL & Jquery

Google like autosuggestion Search box using PHP, MySQL & Jquery

Web is the house of information. Millions are working Day & Night to make the Web better. In this rush to locate correct information from Search engines you required to search wisely. You must noticed sometime it was difficult to find out a suitable search string according to the need. In such case autosuggestion facility works awesome. The king of Search engines Google provides this facility. To implement the same in your application with this demo I created an autosuggestion Search box using PHP and Jquery. Data I am fetching from MySQL Database.

To create this demo here I am with 4 files index.htm, main.css, loading.js & live-Search.php. The HTML file contains a Container which hold an input control with id txtSearch. Below the Search box I have a div to display autosuggestion strings. Initial during page load I hided this using CSS display:none.

index.htm

<!DOCTYPE html>
<html lang="en">
<head>
<title>Google like autosuggestion Search box using PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script src="loading.js"></script>
<link rel="stylesheet" href="main.css" />
</head>
<body>
<div class="container">
<input type="text" id="txtSearch" placeholder="Search our Faculty Database" /><br /> 
<div id="SearchResults"></div>
</div> 
</body>
</html>

The below CSS file gives look n feel to my HTML elements. Here depending upon the width of Search box I applied the same to Search result div.

main.css

.container { margin: 0 auto; padding-top: 150px; width: 600px; }
#txtSearch { width:600px; border:solid 1px #000000; padding:10px; font-size:16px; }
#SearchResults { position:absolute; width:600px; padding:10px; display:none; margin-top:-1px; border-top:0px; overflow:hidden; border:1px #CCC solid; background: #FFF; }
.res-pan { border-bottom: 1px dashed #999; font-size: 16px; height: 40px; }
.res-pan:hover { background: #2E237A; cursor:pointer; color:#FFFFFF; }
.live-results { width: 100%; height: 100%; padding-top: 8px; }

Core logic for autosuggestion Search box is running inside the below JS file. Here using Jquery I am tracking the keyup event of the Search box. Inside this using Jquery Ajax method I am sending user inputs to live-Search.php. To maintain secure data transmission in Ajax call I used POST method.

loading.js

$(document).ready(function(){
/* During page load Clearing the Search Box value */
$("#txtSearch").val('');
/* Setting Focus to Search Box */
$("#txtSearch").focus();
/* Handling Keyup event for Search Box */
$("#txtSearch").keyup(function(){
var queryStr = $(this).val();
var dataString = 'SearchString='+ queryStr;
if(queryStr!='')
{
$.ajax({
type: "POST",
url: "live-Search.php",
data: dataString,
cache: false,
success: function(html)
{
$("#SearchResults").html(html).show();
$(".live-results").on("click",function() {	
$("#txtSearch").val($(this).html());
$("#SearchResults").fadeOut();
});
}
});
}
return false; 
});
$(document).on("click", function(e) { 
var clicked = $(e.target);
if (!clicked.hasClass("live-results")){
$("#SearchResults").fadeOut(); 
}
});
});

In success event of Jquery Ajax method I am binding the response data to SearchResults div. Here my response data is pure HTML. Which is programmed in below PHP file. This HTML contains a div with class name live-results. After displaying autosuggestion pan to handle user selection I am assigning the selected value to Search box inside the live-results click event. Once the selected value mapped to Search box I am hiding the Search results pan.

live-Search.php

<?php
$connection = mysql_connect("localhost", "root");
mysql_select_db("demo_db", $connection);
if($_POST)
{
$q = $_POST['SearchString'];
$sqlRes = mysql_query("select faculty_id,faculty_name from faculty where faculty_name like '%$q%' order by faculty_id LIMIT 5");
while($row=mysql_fetch_array($sqlRes))
{
$username   = $row['faculty_name'];
?>
<div class="res-pan">
<div class="live-results"><?php echo $username; ?></div>
</div>
<?php
}
}
?>

The above PHP file is fetching user inputs from Jquery Ajax method using $_POST[‘SearchString’]. Then using a MySQL Select query I am using wild-char Search with limit of 5 records. Then displaying those data inside a while loop using div. To apply CSS on these div’s I assigned Classes to them.

For demo purpose in MySQL create a db with name “demo_db”. Then using the following query Create a table for Faculty.

CREATE TABLE Faculty (
Faculty_ID INT NOT NULL AUTO_INCREMENT,
Faculty_Name VARCHAR(70) NOT NULL,
Email_ID VARCHAR(60) NOT NULL,
Mobile_Number VARCHAR(10) NOT NULL,
PRIMARY KEY (Faculty_ID)
);

Once the table Created successfully insert data using the following SQL statements.

INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Abhishek Bachchan','abhishek@gmail.com','9823234566');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Bijayalaxmi Sahoo','bijayalaxmi@gmail.com','8773325680');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Manmohan Desai','manmohan.desai@gmail.com','7734698800');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Naveen Patnaik','naveen.p@gmail.com','8775600027');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Poonam Jhawar','poonam.jhawar@gmail.com','9096266548');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Poonam Pandey','poonam.p@gmail.com','8772282800');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Ravi Shankar','ravi.shankar@gmail.com','9258733647');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Nibedita Biswas','nibedita.biswas@gmail.com','9227855677');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Gitanjali Swamy','gitanjali@gmail.com','8227699000');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Ravi Srivastab','ravi.srivastab@gmail.com','7433309768');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Rani Mukherjee','rani.mukherjee@gmail.com','8227899003');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Meghana Kaushik','meghana.k@gmail.com','8923764466');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Rituraj Mohanty','rituraj.mohanty@gmail.com','9096874588');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Meghana Raj','meghana.raj@gmail.com','9896355454');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Sanchita Shetty','sanchita.shetty@gmail.com','7878654321');
INSERT INTO faculty(Faculty_Name, Email_ID, Mobile_Number) VALUES ('Ramya Krishnan','ramya.krishnan@gmail.com','9888766543');

Hope the above explanation will make you clear about the logic behind this demo app. Enrich your application Search facility with autosuggestion. This will make your application much user friendly.