You 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 search input box.
In this tutorial we’re going to create a live search box that will search the countries table and show the results asynchronously. But, first of all we need to create this table.
Search value from Input box in mysql php
To find any kind of value from any database, you will need, first database from which you will search the value, second you want to implement this script in which index file, you want that page, as well as a hidden-search.php file has to be created from where the output of the value entered by the visitor will be forwarded to you on the index.php page
Stage 1: Making the DataBase Table In PHP My Admin
Execute the accompanying SQL question to make the nations table in your MySQL data set.
CREATE TABLE state_name (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);In the wake of making the table, you want to populate it for certain information utilizing the SQL Supplement articulation. On the other hand, you can download the prepopulated nations table by tapping the download button and import it in your MySQL data set.
Stage 2: Making the Pursuit Structure
Presently, we should make a basic web interface that permits client to live hunt the names of nations accessible in our nations table, very much like an autocomplete or typeahead.
Make a PHP record named “index.php” and put the accompanying code within it.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Live MySQL Database Search</title>
<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("hidden-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="search-box">
<input type="text" autocomplete="off" placeholder="Search country..." />
<div class="result"></div>
</div>
</body>
</html>
Each time the substance of search input is changed or keyup occasion happen on search input the jQuery code (line no-47 to 67) sent an Ajax solicitation to the “backend-search.php” document which recovers the records from nations table connected with the looked through term. Those records later will be embedded inside a <div> by the jQuery and showed on the program.
Stage 3: Handling Search Question in Backend
Also, here’s the source code of our “hidden-search.php” document which look through the data set in light of question string sent by the Ajax demand and send the outcomes back to program.
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$coorection = mysqli_connect("localhost", "user", "password", "databasename");
// Check connection
if($coorection === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
if(isset($_REQUEST["term"])){
// Prepare a select statement
$sql = "SELECT * FROM state_name WHERE name LIKE ?";
if($stmt = mysqli_prepare($coorection, $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($coorection);
}
}
// Close statement
mysqli_stmt_close($stmt);
}
// close connection
mysqli_close($coorection);
?>The SQL SELECT proclamation is utilized in blend with the LIKE administrator (line no-16) to find the matching records in nations data set table. We’ve carried out the pre-arranged articulation for better hunt execution as well as to forestall the SQL infusion assault.