Autocomplete search box is a feature that suggests relevant results as the user types in the search box. It is a very useful feature as it helps the user to quickly find the desired results from a large list of options.
In order to implement an autocomplete search box in PHP, MySQL and Ajax, we need to create a database table to store the data, create a PHP script to query the database and return the results, and create an Ajax script to send the user input to the server and display the results.
First, we need to create a database table to store the data. We can use the following SQL query to create a table called ‘autocomplete’:
CREATE TABLE autocomplete (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
Next, we need to create a PHP script to query the database and return the results. We can use the following code to create a PHP script called ‘autocomplete.php’:
$row[‘name’],
‘value’ => $row[‘name’]
);
}
// Return the response in JSON format
echo json_encode($response);
?>
Finally, we need to create an Ajax script to send the user input to the server and display the results. We can use the following code to create an Ajax script called ‘autocomplete.js’:
$(document).ready(function() {
$(‘#search’).keyup(function() {
var term = $(this).val();
$.ajax({
url: ‘autocomplete.php’,
type: ‘GET’,
data: {
term: term
},
dataType: ‘json’,
success: function(data) {
$(‘#results’).html(”);
$.each(data, function(key, value) {
$(‘#results’).append(‘
‘);
});
}
});
});
});
With these steps, we can successfully implement an autocomplete search box in PHP, MySQL and Ajax.
Sometimes, you need to search for data without loading the whole page. This tutorial will guide you in detail on how you can implement live autocomplete textbox search in PHP MySQL using jQuery ajax with bootstrap form. This tutorial shows you an easy way to autocomplete search with PHP MySQL using jQuery ajax with bootstrap.
To create autocomplete search in php mysql ajax. In this tutorial, you will learn how to create a live autocomplete search textbox in the PHP MySQL database using jQuery Ajax.
Autocomplete Textbox Live Search using jQuery Ajax in PHP MySQL
Just follow the few below steps and easily implement the live autocomplete textbox search using jquery Ajax in php mysql:
- Step 1 – Create a Database Connection File
- Step 2 – Create an Autocomplete search form
- Step 3 – Create a PHP Script for Search to Database
Step 1 – Create a Database Connection File
In this step, you will create a file name db.php and update the below code into your file.
The below code is used to create a MySQL database connection in PHP. When we search from MySQL database using PHP, there we will include this file:
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "my_db";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect MySql Server:' .mysql_error());
}
?>
Step 2 – Create an Autocomplete search form
In this step, we need to create an autocomplete textbox search form and update the below code into your autocomplete search box in PHP MySQL.
<!Doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Autocomplete Textbox using jQuery AJAX in PHP MySql - Tutsmake</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
</head>
<style type="text/css">
ul{
margin-top: 0px;
background: #fff;
color: #000;
}
li{
padding: 12px;
cursor: pointer;
color: black;
}
li:hover{
background: #f0f0f0;
}
</style>
<body style="background-color: #ebebeb">
<div class="container" style="margin-top: 50px;">
<h2 class="text-center">Autocomplete Textbox using jQuery AJAX in PHP MySql</h2>
<div class="row">
<div class="col-md-3"></div>
<div class="col-md-6" style="margin-top:20px; margin-bottom:20px;">
<form>
<div class="form-group">
<input type="text" class="form-control" name="search" id="search" placeholder="Search Users">
<div id="search-result"></div>
</div>
</form>
</div>
</div>
</div>
</body>
</html>
<!--- Autocomplete textbox jquery ajax --->
<script type="text/javascript">
$(document).ready(function(){
$("#search").on("keyup", function(){
var search = $(this).val();
if (search !=="") {
$.ajax({
url:" ajax-db-search.php",
type:"POST",
cache:false,
data:{term:search},
success:function(data){
$("#search-result").html(data);
$("#search-result").fadeIn();
}
});
}else{
$("#search-result").html("");
$("#search-result").fadeOut();
}
});
// click one particular search name it's fill in textbox
$(document).on("click","li", function(){
$('#search').val($(this).text());
$('#search-result').fadeOut("fast");
});
});
</script>
After that, don’t forget to update the below-given CSS and JS library in your autocomplete search box form:
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js">
Step 3 – Create a PHP Script for Search to DB
In this step, you need to create one file name ajax-db-search.php and update the below code into your file.
The below code is to search into a MySQL database table using an Ajax PHP script:
<?php
require_once "db.php";
if (isset($_POST['term'])) {
$query = "SELECT * FROM users WHERE name LIKE '{$_GET['term']}%' LIMIT 25";
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
while ($user = mysqli_fetch_array($result)) {
$res[] = $user['name'];
}
} else {
$res = array();
}
//return json res
echo json_encode($res);
}
?>
Conclusion
In this tutorial, you have learned how to implement an autocomplete textbox search in PHP MySQL from the database table using jQuery ajax.
The autocomplete textbox using jquery ajax in php mysql look like:
This is a very useful, important and easy example of Autocomplete Search textbox or input box in PHP MySQL using jQuery ajax.
Recommended PHP Posts
If you have any questions or thoughts to share, use the comment form below to reach us.
