Hello friends! We’re here again to discuss live search functionality in a dynamic website using php and javascript. Previously, we had a post on the search operation using only php. We recommend to go through it first, at here. Now, we’ll see as the data is typed, the suggestions will come under it. So, without wasting time, lets start how to do this.
Creating search takes 3 steps:
- Create the Database.
- Create live search front UI.
- Add the back end code.
let us see them one by one
1. Creating Database
First things first! Lets create a Database using the following query. We are creating a table ’employee’ in which id, name, email and company name data details exists.
CREATE TABLE IF NOT EXISTS `employee` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `email` varchar(100) NOT NULL, `company` varchar(100) NOT NULL )
Lets Fill some records in the table.
INSERT INTO `employee` (`id`, `name`, `email`, `company`) VALUES (1, 'Mike', 'mike@softglobe.net', 'Softglobe'), (2, 'James', 'james@gmail.com', 'Google'), (3, 'Mark', 'mark123@microsoft.com', 'Microsoft'), (4, 'Albert', 'albert29@softglobe.net', 'Softglobe'), (5, 'Smith', 'smith@nokia.com', 'Nokia'), (6, 'Robert', 'robert@tcs.com', 'TCS'), (7, 'Raffel', 'raffel@cognizant.com', 'Cognizant'), (8, 'John', 'john123@essel.com', 'Essel'), (9, 'Peter', 'peter@hp.com', 'HP'), (10, 'Barton', 'barton@apple.com', 'Apple');
2. Creating Live search using javascript
To make this tutorial short and easy, we have made a single file for our front end designing. It includes front UI, css and connection code. So create a file search-form.php and paste the following code in it.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Live Search using PHP, MySQL Database and Javascript</title>
<link rel="stylesheet" href="css/w3.css">
<style type="text/css">
body{
font-family: Arail, sans-serif;
}
/* Formatting search box */
.search-box{
width: 300px;
position: relative;
display: inline-block;
font-size: 14px;
}
.search-box input[type="text"]{
height: 32px;
padding: 5px 10px;
border: 1px solid #CCCCCC;
font-size: 14px;
}
.result{
position: absolute;
z-index: 999;
top: 100%;
left: 0;
background: white;
}
.search-box input[type="text"], .result{
width: 100%;
box-sizing: border-box;
}
/* Formatting result items */
.result p{
margin: 0;
padding: 7px 10px;
border: 1px solid #CCCCCC;
border-top: none;
cursor: pointer;
}
.result p:hover{
background: #f2f2f2;
}
</style>
<script src="js/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$('.search-box input[type="text"]').on("keyup input", function(){
/* Get input value on change */
var inputVal = $(this).val();
var resultDropdown = $(this).siblings(".result");
if(inputVal.length){
$.get("backend-search.php", {term: inputVal}).done(function(data){
// Display the returned data in browser
resultDropdown.html(data);
});
} else{
resultDropdown.empty();
}
});
// Set search input value on click of result item
$(document).on("click", ".result p", function(){
$(this).parents(".search-box").find('input[type="text"]').val($(this).text());
$(this).parent(".result").empty();
});
});
</script>
</head>
<body>
<center>
<div class="w3-container w3-teal">
<h1>Live Search using PHP, MySQL Database and Javascript</h1>
</div>
<div class="w3-container">
<p>Enter the queries to search in the search box.</p>
<div class="search-box">
<input type="text" autocomplete="off" placeholder="Search Employee Name..." />
<div class="result"></div>
</div>
</div><br/>
<div class="w3-container w3-teal">
<p>An amazing tutorial on <a href="https://technopoints.co.in">Technopoints</a></p>
</div>
</center>
</body>
</html>
Make sure you have javascript files in your folder for proper working of the code. You can get them, form internet very easily. Just Google them.
We are not here using session, but you can easily implement this functionality in your session projects also.
3. Add the backend
Now, we need a special file to fetch the data from database and return it to the main file. So lets Create a file backend-search.php and add the following code in it. This is the last step to create the live search functionality.
<?php
/*connection code. Change the folowing credentials with yours.*/
try{
$pdo = new PDO("mysql:host=localhost;dbname=search", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt search query execution
try{
if(isset($_REQUEST['term'])){
// create prepared statement
$sql = "SELECT * FROM employee WHERE name LIKE :term";
$stmt = $pdo->prepare($sql);
$term = $_REQUEST['term'] . '%';
// bind parameters to statement
$stmt->bindParam(':term', $term);
// execute the prepared statement
$stmt->execute();
if($stmt->rowCount() > 0){
while($row = $stmt->fetch()){
echo "<p>" . $row['name'] . "</p>";
}
} else{
echo "<p>No results found</p>";
}
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>
In the above file, we are using a PDO connection method for connecting with the database because it is more secure and prevents SQL injections. We have used a word “LIKE” in an SQL query to fetch the related names as we type characters in the search box.
The REQUEST super global variable is used to let the system know that the fetch operation should be started as soon as the user starts typing in the text box. the suggestions appears under the text box appears within a fraction of second.
We’ve added a styling using w3.css classes. You can get it by downloading the source code. The following image is a view of a homepage of our tutorial project.
Now visit the search-form.php file, You can directly put any employee name’s starting letter, the matching phrases will be displayed automatically. Even you can insert rows in one tab and perform fetching without refreshing the page as shown below image.
Looking nice na? Why not try it now? The source code is always available for free of cost. You can directly download it from the below link.
2 thoughts on “Live Search using PHP, MySQL Database and Javascript”