Welcome dear readers, this article is going to focus on explaining autocomplete search box in PHP and MySQL. I know almost everyone uses Google to search the web. You may have noticed suggestions appearing when you start typing any keyword in Google’s search box. It is actually an autocomplete search box. This functionality makes it easy for users to search your content and increases user engagement. Let’s learn how to build such an autocomplete search box using jquery ajax and PHP.
How to build autocomplete search box in PHP MySQL and ajax?
I am dividing the complete concept into different steps to make this guide easily understandable.
- Create a search box form with a submit button.
- Write a key-up event and fetch suggestions.
- Create a list click event to load suggestions into the search box.
- Fetch the search results by creating a submit button click event.
Now it’s time to perform all the above-mentioned steps practically one by one. Don’t worry! I will share the code also at the end of the article.
How to create a auto complete search box form using bootstrap?
It’s basically an input text field where users will write their search queries. I am going to use bootstrap to create this search box.
- Create an input text field inside the HTML form tag and set its id to “search”.
- Set the autocomplete attribute to “off” to disable the browser autosuggestions.
- Also, create a submit button and use a search icon instead of text to beautify the search box.
- Set the id attribute of the submit button to “submit”. We will use these id’s in the jquery ajax part later in this article.
- Besides the search box, we need two more divisions with id’s “list” and “card”. The list div will be used to show a suggestion list and the card will be the place to display search results.
<div class="row justify-content-center my-5 ">
<div class="col-6 text-center">
<form>
<div class="input-group mb-3">
<input type="text" id="search" class="form-control form-control-lg" placeholder="Search Here" autocomplete="off">
<button type="submit" id="submit" class="input-group-text btn-success px-4"><i class="fa fa-search"></i></button>
</div>
</form>
<div id="list"></div>
</div>
</div>
<div class="row justify-content-center">
<div class="col-6">
<div id="card"> </div>
</div>
</div>
How to fetch Auto complete search box suggestions using ajax and PHP?
This step is the core part of this article. In this part, we are actually going to create a key-up event. whenever a user presses a key, suggestions will be fetched using an ajax request and PHP script.
- First, create a key-up event on the search box when the document is ready using jquery.
- Store the data of the search box in the “search_query” variable and then write an ajax call if the search query is not empty.
- If the search_query is empty, fade out the list div. It is because when the user erases his search query, the suggestion list will automatically disappear.
- In the success part of the ajax call, display the returned list of suggestions in the “list” div using html() function.
<script>
$(document).ready(function() {
$("#search").keyup(function(e) {
$("#card").html("");
var search_query = $(this).val();
if (search_query != "") {
$.ajax({
url: "includes/ReturnList.inc.php",
type: "POST",
data: {
search: search_query
},
success: function($data) {
$("#list").fadeIn('fast').html($data);
}
});
} else {
$("#list").fadeOut();
}
});
});
</script>
That’s all about jquery and ajax call. Now see how to search and fetch suggestions in the database using PHP.
- Since we are fetching data from the database, we obviously need a database connection.
- Now write an SQL select query to fetch only unique records from the database using a distinct clause.
- Use like operator instead of equal to in the where clause to fetch record based on maximum similarity.
- Execute the query and fetch the data from the database.
- Arrange the data in the form of a list inside a $html variable using concatenation.
<?php
if (!empty($_POST['search'])) {
$Search_Query = $conn->real_escape_string($_POST['search']);
$query = "SELECT distinct(title) FROM books
WHERE title LIKE '%{$Search_Query}%'; ";
$result = $conn->query($query) or die($conn->error);
$html ='<ul class="list-group" style="margin-top:-15px;">';
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$html .= "<li class='list-group-item'><a>" . $row['title'] . "</a></li>";
}
} else {
$html .= '<li class="list-group-item">Sorry! No record found</li>';
}
$html .= "</ul>";
echo $html;
}
$conn->close();
Now see how this code will work. First, when the user enters anything in the search box, the search query will be sent to the PHP page using ajax call. Unique suggestions will be fetched on the PHP page and sent back to ajax in a bootstrap list format. In the ajax success part, the received list will be displayed to users in the “list” div.
How to load the suggested keywords in the auto complete search box?
As the user enters anything, suggestions will be displayed to him. Now the user must be able to load any suggestion in the search box on click. In this part, we will add a click event of list li. So when the user clicks on any list item, its text will be loaded in the search box.
- Create a click event on li of the unordered list.
- Set the value of the search box to the text inside the list item.
- Also, fade out the suggestion list using the fadeout() function.
<script>
$(document).ready(function() {
$(document).on("click", "#list li", function() {
$("#search").val($(this).text());
$("#list").fadeOut();
});
});
</script>
Now when the user clicks on any suggestion, the list item text will be loaded in the search box and the suggestion list will disappear.
How to submit search queries and fetch search results?
This is the final and interesting part of the article. When the user clicks the submit button, the search query will be sent to a PHP page through an ajax post request. Data related to the search query will be fetched but this time arrange inside bootstrap card format. finally, the success part of the ajax request will display the records in the “card” div.
- Create a click event on the submit button and then fetch the query from the search box in a search_query variable.
- Send the variable to the PHP page through an ajax request.
- Upon the success of the ajax call, display the search results received in the “card” div.
<script>
$(document).ready(function() {
$("#submit").on("click", function(e) {
e.preventDefault();
$("#card").html("");
var search_query = $('#search').val();
$.ajax({
url: "includes/autocomplete.inc.php",
type: "POST",
data: {
search: search_query
},
success: function($data) {
$("#card").fadeIn('fast').html($data);
}
});
$("#search").val("");
});
});
</script>
That’s the details of the ajax call. Now let’s see how the PHP script will fetch data.
- Now inside the PHP script, execute an SQL select query with like operator instead of equal in the where clause.
- Retrieve the records in a bootstrap card format inside the $html variable.
- Echo the $Html variable to send the search results back to ajax.
<?php
if (!empty($_POST['search'])) {
$Search_Query = $conn->real_escape_string($_POST['search']);
$query = "SELECT * FROM books
WHERE title like '%{$Search_Query}%'; ";
$result = $conn->query($query) or die($conn->error);
$html = "";
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$html .= '<div class="card my-4 ">';
$html .= ' <h4 class="card-title my-2 mx-3 ">' . $row['title'] . '</h4>';
$html .= ' <h6 class="card-subtitle my-2 mx-3">' . $row['author'] . '</h6>';
$html .= '<p class="card-text my-2 mx-3" >' . $row['description'] . '</p>';
$html .= "</div>";
}
} else {
$html .= "Sorry! No record found";
}
echo $html;
}
$conn->close();
That’s it we have completed all the steps to build autocomplete search box in PHP MySQL.
Output of the code:
We have completed our ajax autocomplete search in PHP successfully. I tried to explain everything related to autocomplete stepwise. You can enhance the functionality of this example further using your creativity. I hope you liked this information. Thanks for reading and supporting us.