Setup Menus in Admin Panel

  • No products in the cart.

  • LOGIN

AJAX Database Live Search

Ajax Live Database Search:


We can create a simple live database search functionality utilizing the Ajax and PHP, where the search results will be displayed as you start typing some character in the search input box.

In this section, we’re going to create a live search box that will search the fruits table and show the results asynchronously. But, first of all, we need to create a table.

Step 1:

Creating the Database Table
Execute the following SQL query to create the fruits table in your MySQL database.

Example:

CREATE TABLE fruits (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);

After creating the table, you can download the cheap fruits table by clicking the download button and import it into your MySQL database.

 

Step 2:

Creating the Search Form
Now, create a simple web interface that allows a user to search the names of fruits available in our fruits table, just like an autocomplete or typeahead.

Create a PHP file named “search.php” and put the following code inside of it.

Example:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Live Database Search</title>
<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;
}
.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="https://code.jquery.com/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("server.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>
<div class="search-box">
<input type="text" autocomplete="off" placeholder="Search fruits..." />
<div class="result"></div>
</div>
</body>
</html>

Every time the content of search input is changed on search input the jQuery code sends an Ajax request to the “server.php” file which retrieves the records from countries table related to the searched term. Those records later will be inserted inside a <div> by the jQuery and displayed on the browser.

 

Step 3:

Processing Search Query in Backend
And here’s the source code of our “server.php” file which searches the database based on query string sent by the Ajax request and sends the results back to a browser.

Example

server.php

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
if(isset($_REQUEST['term'])){
// Prepare a select statement
$sql = "SELECT * FROM countries WHERE name LIKE ?";

if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "s", $param_term);

// Set parameters
$param_term = $_REQUEST['term'] . '%';

// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);

// Check number of rows in the result set
if(mysqli_num_rows($result) > 0){
// Fetch result rows as an associative array
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
}
// Close statement
mysqli_stmt_close($stmt);
}
// close connection
mysqli_close($link);
?>

 

The SQL SELECT statement is used in combination with the LIKE operator (line no-16) to find the matching records in fruits database table. We’ve implemented the prepared statement for better search performance as well as to prevent the SQL injection attack.

SEE ALL Add a note
YOU
Add your Comment
 

Advanced Course Search Widget

Popular Courses

Course Categories

© PHPhelp. 2002 - 2018 All rights reserved.
X