Ajax live data search using jQuery PHP MySQL Codes With Pankaj
3 min readAug 31, 2023
Step 1: Creating the Database Table
Step 2: Creating the Search Form
Step 3: Processing Search Query in BackendStep 1: Creating the Database Table
create database p4n;
use p4n;
CREATE TABLE countries (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
INSERT INTO countries (name) VALUES
("India"),
("United States"),
("China"),
("Brazil"),
("Russia"),
("France"),
("Germany"),
("Japan"),
("United Kingdom"),
("Canada");
select * from countries;Step 2: Creating the Search Form
Create a PHP file named “search-form.php”
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>MySQL Database Search @p4n.in</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-4bw+/aepP/YC94hEpVNVgiZdgIC5+VKNBQNGCHeKRQN+PtmoHDEXuppvnDJzQIu9" crossorigin="anonymous">
<style>
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-3.5.1.min.js"></script>
<script>
$(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>
<div class="px-4 py-5 my-5 text-center">
<a href="https://www.codeswithpankaj.com/"> <h1 class="display-5 fw-bold text-body-emphasis">Codes With Pankaj</h1><a>
<div class="col-lg-6 mx-auto">
<p class="lead mb-4">PHP Live MySQL Database Search Country</p>
<div class="d-grid gap-2 d-sm-flex justify-content-sm-center">
<div class="search-box">
<input type="text" autocomplete="off" placeholder="Search country..." />
<div class="result"></div>
</div>
</div>
</div>
</div>
</body>
</html>Step 3: Processing Search Query in Backend
the source code of our “backend-search.php”
<?php
/* MySQL server connection.
MySQL server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "admin", "p4n");
// 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);
?>
