Ajax Live Search With PHP And MySQL

Today I’m going to create a basic example of an ajax live search with PHP and MySQL. Live search is an essential aspect of UI and user experience on the website. Normally you would connect this into a database using an engine like MySQL, but for this post, I want to focus on handling the response from PHP, and in this post, I will highlight the process of building an autocomplete (Live Search) engine for your website.

AJAX Live Search is a search form that displays the result as you type. It acts like the Google Search Autocomplete (Auto Suggest) feature. Therefore it makes it easier for the users to find what they are looking for. AJAX Live Search has been developed using PHP, JavaScript (jQuery), AJAX, and MySQL.

What is Ajax?

AJAX is the acronym for Asynchronous JavaScript & XML.

JavaScript is an open source & most popular client-side scripting language supported by all browsers. This class is geared to make you a JavaScript pro!.

XML is the acronym for Extensible Markup Language. It is used to encode messages in both human and machine-readable formats.

It is a technology that reduces the interactions between the server and the client.

Ajax Live Search

I will first build the code on my local machine. For this, I will need a MySQL database with sample data and connect it with the AJAX form. Please first create a database named Tutorial on your pc and create a user table in it, follow the below example.

Step 1 – Create a Database

  1. Open PHPMyAdmin.
  2. Create a database as a “tutorial”.
  3. Click create table and name it as “user”.

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `user_name` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Add 10 records to your above database table.

Step 2 – Create A New File config.php

Database configuration file edit database name, user, and password per your configuration.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "tutorial";

  // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);

  // Check connection
  if ($conn->connect_error) 
  {
      die("Connection failed: " . $conn->connect_error);
  }

Step 3 – Create A New File index.php

This is the main file of the search engine, where the user inputs data and views the result. Create a file named index.php and paste the following code into it.

<!DOCTYPE html>
<html>
<head>
    <title>Ajex Live Search in  PHP with Jquery</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <link rel="stylesheet" type="text/css" href="custom.css">
    <script type="text/javascript" src="custom.js"></script>
</head>
<body>
    <div class="wrap">
        <div class="search">
	    <h2>Ajex Live Search in  PHP with Jquery</h2>
	    <input type="text" class="searchTerm" placeholder="Enter name">
	    <button type="submit" class="searchButton">
	        <i class="fa fa-search"></i>
	    </button>
	</div>
	<div class="search_box">
  
        </div>
    </div>
</body>
</html>

Step 4 – Create A New File custom.js

Whenever the content of search input is changed or keyup event occurs on search input the below jQuery custom.js” file retrieves the records from the countries table related to the searched term. Those records later will be inserted inside a <ul> by jQuery and displayed on the browser.

$(document).ready(function(){
    $(".searchTerm").keyup(function(){
        var text = $(this).val();
        $.ajax({
            type: "POST",
            url: "search.php",
            data: 'text='+text,
            dataType: "html",
            async: false,
            success: function(text){
                $('.search_box').html(text);
            }

        });
    });
});

Step 5 – Create A New File search.php

This is the important file that generates the AJAX requests for the server and returns the results. Create a new file named search.php, and copy the following code into it.

<?php
require_once('config.php');
$text = $_POST['text'];

$result = $conn->query("SELECT * FROM user where user_name LIKE '%$text%'");

	if($result->num_rows > 0){
		echo '<ul class="search_ul">';
		while ($row = $result->fetch_assoc()) 
		{
			echo '<li><a href="">'.$row["user_name"].'</a></li>';
		}
		echo '</ul>';
	}
?>

The SQL SELECT statement is used in combination with the LIKE operator (line no-5) to find the matching records in the users_name database table.

Step 6 – Create A New File custom.css

Below define the search box design in custom.css file

@import url(https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css);

.search {
  width: 100%;
  position: relative
}
.searchTerm {
  float: left;
  width: 100%;
  border: 3px solid #00B4CC;
  padding: 5px;
  height: 21px;
  outline: none;
  color: #9DBFAF;
}
.searchTerm:focus{
  color: #00B4CC;
}
.searchButton {
  position: absolute;  
  right: -50px;
  width: 40px;
  height: 37px;
  border: 1px solid #00B4CC;
  background: #00B4CC;
  text-align: center;
  color: #fff;
  border-radius: 5px;
  cursor: pointer;
  font-size: 20px;
}

/*Resize the wrap to see the search bar change!*/
.wrap{
  width: 36%;
  position: absolute;
  top: 15%;
  left: 50%;
  transform: translate(-50%, -50%);
}
.search_box{
  width: 102.2%;
  background: #fff;
}
.search_box ul{
  list-style: none;
  padding: 0px;
  width: 100%;
  box-shadow: 0px 0px 4px 0px #00B4CC;
  margin-left: 1px;
}
.search_box ul li{
  padding: 5px 10px;
  box-shadow: 0px 0px 1px 0px #00B4CC;
}
.search_box ul li:hover{
  background: #E2FCFF;
}
.search_box ul li a{
  text-decoration: none;
}

Conclusion

In this tutorial, we talk about AJAX and we saw Ajax Live Search With PHP And MySQL.

That’s all for today hope you like the tutorial if you have any queries regarding this article so feel free to contact us below. Thanks for reading.

Leave a Comment