Ajax Live Data Search using PHP and MySQL

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

1 What is Live Search?
2 Creating Our Database
3 Connection Page
4 Main Screen
5 Search Query in Back-end

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.

Comments

Sonu says

April 4, 2018 at 12:25 pm

What i exactly searching. Thanks for sharing great piece of code. keep posting

- Jamaley Hussain says
  
  April 7, 2018 at 11:25 am
  
  Hi Sonu,
  I’m glad to know that you like it. thanks for stopping here.
  Keep visiting.
  
Moss says

April 5, 2018 at 2:47 pm

Hi Abhishek,
Coding is one of the best skills anyone in the online world need to learn. It actually allows you to do a lot more than you can imagine. I’m actually not into coding, but it is a must try for me.

Thanks for sharing.

- Abhishek Kumar says
  
  April 7, 2018 at 5:28 pm
  
  Thank you Moss
  That you like the post.
  keep visiting
  
Sergiy says

June 28, 2018 at 6:16 pm

Hi, Abhishek Kumar. Nice code, thanks.

Plz, can u help me, How can I do individual column live search?

- Abhishek Kumar says
  
  June 30, 2018 at 12:25 pm
  
  Thanks Sergiy for the your feedback..
  and for individual column search you have to just
  {
  $search = mysqli_real_escape_string($con, $_POST[“query”]);
  $query = “SELECT * FROM user //enter your mySqQL query here
  WHERE id LIKE ‘%”.$search.”%’
  OR first_name LIKE ‘%”.$search.”%’
  “;}
  and for multiple search you can visit to this link
  http://www.techjunkgigs.com/display-data-from-mysql-database-using-php-jquery-and-datatable/
  
  - Sergiy says
    
    July 3, 2018 at 8:33 pm
    
    Abhishek Kumar, without “OR”, individual.
    
    1st input search – searching data from 1st table column – only First name,
    2nd input search – searching data from 2st table column – only Last name,
    3st input search – searching data from 3rd table column – only Age,
    4 input search – searching data from 4 table column – only phone number.
    
    I do this, added add2.php with code for seraching by 2 column, and code load_data2 at JS, but it many rows of code….
    
    Datatables script its good, but not for me. Thanks.
    
Pistone Junior Sanjama says

May 17, 2019 at 3:15 am

Thanks for sharing this code. i will customize itso that the results shown can be clicked

Paari Dhanush says

June 26, 2019 at 3:12 pm

super! clear code and it’s very easy to understand also.

Salma Baalawy says

July 23, 2019 at 6:06 am

Thanks very much appreciated

Chris says

September 14, 2019 at 1:36 pm

Thank for this – really useful!

rasiga says

June 10, 2022 at 10:38 am

Hi Abishek,
thank you so much for sharing this post.it is a very useful and informative blog about coding. now I got to learn some new skills.