In this tutorial, we are going to learn how we can make our own live search. Live Search has been developed using PHP, JavaScript (jQuery), AJAX and MySQL.
Table of Contents
What is Live Search?
A live Search is a search form which displays the result as you type. It acts like the Google Search Autocomplete or we can say the AutoSuggest feature. So, it makes it easier for the users to find what they are searching for. Now a day Live search is an essential aspect of any user interface and user experience on the website. In this tutorial, we are going to highlight the process of building the Live Search engine for your website.
As compared to traditional search live search has many benefits like:
As you type anything then Result is displayed on the screen.
The result becomes narrow as you continue typing
When results become too narrow, then remove characters to see a broader result.
Creating Our Database
Firstly, we have to create a database to store the data, for creating database follow these steps:
- Open phpMyAdmin
- Select database icon and Create a new database with the name after that
- Create the table with the table name
- And then insert the details Or
- After creating a database name, click the SQL and paste the following code.
CREATE TABLE `user` ( `id` int(11) NOT NULL, `first_name` varchar(200) NOT NULL, `last_name` varchar(200) NOT NULL, `age` int(12) NOT NULL, `mobile` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `user` (`id`, `first_name`, `last_name`, `age`, `mobile`) VALUES (1, 'John', 'Smith', 22, '4234534531'), (2, 'Peterson', 'Parker', 40, '8645534531'), (3, 'Rock', 'Madison', 20, '4234534531'), (4, 'Abhi', 'Singh', 23, '4234429784'), (5, 'Swami', 'Naidu', 21, '3747234671'); ALTER TABLE `user` ADD PRIMARY KEY (`id`); ALTER TABLE `user` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11; COMMIT;
This table is to store the details like here I have stored the details of the user.
Connection Page
Config.php
This is a connection page, mysqli_connect() function opens connection to the MySQL server .
<?php
$con = mysqli_connect("localhost", "root", "", "ajax");
?>
Main Screen
This page will appear whenever a user opens the site, this page contains simple web interface that allows the user to live search, where the user has to enter any word or letter to search the whole details just like an autocomplete. This page contains all record of data which is available in MySQL database. Create a PHP file named “index.php” and paste the following code inside of it.
index.php
<?php
include("config.php");
?>
<html>
<head>
<title>Techjunkgigs</title>
<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?libraries=places&language=en"></script>
<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" />
<script>
$(document).ready(function(){
load_data();
function load_data(query)
{
$.ajax({
url:"add.php",
method:"POST",
data:{query:query},
success:function(data)
{
$('#result').html(data);
}
});
}
$('#search').keyup(function(){
var search = $(this).val();
if(search != '')
{
load_data(search);
}
else
{
load_data();
}
});
});
</script>
</head>
<body>
<div class="container-fluid">
<div class="content-wrapper">
<!-- Content Header (Page header) -->
<h1>Techjunkgigs</h1>
<p><h3>Live Search</b></h3>
<div class="wall">
<section class="content">
<div class="box box-default">
<div class="box-header with-border">
</div>
<div class="box-body">
<div class="row">
<div class="col-xs-12">
<input type="text" name="search" id="search" placeholder="Search" class="form-control" />
<div id="result"></div>
</div>
</div>
</div>
</div>
</section>
</div>
</div>
</div>
</body>
</html>
When an input is entered in the search bar then the jQuery sent an Ajax request to the add.php file. And retrieve the search result from the user table and displayed it to the user.
Search Query in Back-end
And this page contains the source code of “add.php” file which searches the database, against the ajax request sends by query string.
<?php
require ('config.php');
$output = '';
if(isset($_POST["query"]))
{
$search = mysqli_real_escape_string($con, $_POST["query"]);
$query = "SELECT * FROM user
WHERE id LIKE '%".$search."%'
OR first_name LIKE '%".$search."%'
OR last_name LIKE '%".$search."%'
OR age LIKE '%".$search."%'
OR mobile LIKE '%".$search."%'
";}
else
{
$query = "SELECT * FROM user";
}
$result = mysqli_query($con, $query);
if(mysqli_num_rows($result) > 0)
{
$output .= '
<div class="table-responsive">
<table class="table table bordered">
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Phone No</th>
</tr>';
while($row1 = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>'.$row1["id"].'</td>
<td>'.$row1["first_name"].'</td>
<td>'.$row1["last_name"].'</td>
<td>'.$row1["age"].'</td>
<td>'.$row1["mobile"].'</td>
</tr>
';
}
echo $output;
}
else
{
echo Record Not Found';
}
?>
Demo Image
You Can Also Check:
Import CSV File into MySQL Database using PHP
How to Customize a Google Map Custom Markers in PHP
Simple Login Page Using PHP and MySQL
Google Charts in PHP with MySQL Database using Google API
Insert Data into MySQL database with PHP and AJAX without refreshing page
I hope this article helped you to know Ajax Live Data Search using PHP and MySQL. To get the latest news and updates follow us on twitter & facebook, subscribe to our YouTube channel. And If you have any query then please let us know by using the comment form.