Welcome to programmopedia! we are going to build an ajax live data search using jquery, PHP, and MySQL in this article. It’s a user-friendly functionality that makes searching easy and simple. When a user types in a character in the search box, it will start displaying the most relevant records to the user. The live search allows the user to search on the keypress rather than submitting the search query using a submit button. So, let’s see how to build an ajax live search in PHP.
How to build Live search in PHP/Ajax?
First, let’s break down the complete process into steps. Here are the steps required.
- Create a database table with some dummy data.
- Create an Html search box form.
- Fetch default data in an HTML table.
- Create a key-up event and send the data to the PHP page using an ajax request.
- Search the most similar records in the database and send the data back to ajax.
Don’t worry. I am going to explain all these steps in detail one by one.
How to Create a database table with some dummy data?
- We need a database table with some dummy data to build our live ajax search.
- I am going to create a database table with four fields. The first field would be the id field with primary key constraints.
- The other three fields are going to be the title, author, and description and this table will save the dummy records of books.
- We can create the table by executing the below-given SQL query.
CREATE TABLE `library`.`books` ( `id` INT(100) NOT NULL AUTO_INCREMENT , `title` VARCHAR(200) NOT NULL , `author` VARCHAR(200) NOT NULL , `description` TEXT NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
- Insert a minimum of 15 dummy records into this table.
How to create a live data search box form in html?
- This step is super simple. Just create a bootstrap row with one col-6 column.
- Then create an input text field and set the autocomplete attribute to false.
- Set the id of the input field to “search”. We will use this id in the jquery ajax part.
<div class="row justify-content-center my-5 ">
<div class="col-6 text-center">
<div class="input-group mb-3">
<input type="text" id="search" autocomplete="off" class="form-control form-control-lg" placeholder="Search Books Here">
</div>
</div>
</div>
How to fetch and display default data?
Until the user has not searched anything, we will show a default table of data. This step is optional but your live search will look awesome if you do it.
- Create a bootstrap row with one col-8 row. This grid will contain the default data table.
- Since we are going to fetch data from the database, we need to connect to the database first.
- Then write an SQL SELECT query and use limit clause to restrict the data to only 6 records.
- Execute the query to display the records in a bootstrap table.
- Create the table header outside the while loop and write the dynamic row inside the loop.
<div class="row justify-content-center my-5 ">
<div class="col-8 text-center">
<table class="table table-bordered" id="table">
<thead class="bg-primary">
<tr>
<th>Id</th>
<th>Title</th>
<th>Author</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<?php
require 'includes/db.inc.php';
$query = "SELECT * From books LIMIT 6 OFFSET 0";
$result = mysqli_query($conn, $query);
if (mysqli_num_rows($result) > 0) {
while ($Row = mysqli_fetch_assoc($result)) {
//displaying data in table rows dynamically
?>
<tr>
<td><?php
echo $Row['id'];
?></td>
<td><?php
echo $Row['title'];
?></td>
<td><?php
echo $Row['author'];
?></td>
<td><?php
echo $Row['description'];
?></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
</div>
</div>
How to send ajax live data search request on key up ?
This part of the article is the most important one. we are going to generate an ajax request when the user presses any key in the search box.
- When the document is ready, create a key-up event in Jquery by selecting the search box using the id selector.
- Reset the table using jquery trigger() function. Now the default table data will disappear.
- Store the search query from the search box into a variable “search_query”.
- Create an ajax post request to “LiveSearch.inc.php” and pass the search_query variable.
- On the success of the ajax request, display the received data in the bootstrap table using the jquery html() function.
<script>
$(document).ready(function() {
$("#search").on("keyup", function(e) {
$("#table").trigger("reset");
var search_query = $(this).val();
$.ajax({
url: "includes/LiveSearch.inc.php",
type: "POST",
data: {
search: search_query
},
success: function($data) {
$("#table").html($data);
}
});
});
});
</script>
How to search for the most related records in a database?
This step is also a crucial part of our live search article. Based on the search query received through ajax request, we will search for the most related records and display them to the user in a tabular format.
- As we are going to fetch records from the database, database connectivity is required again. This time I am sharing the object-oriented method to connect to the database.
<?php
$server_name = "localhost";
$db_username = "root";
$db_password = "";
$db_name = "library";
$conn = new mysqli($server_name, $db_username, $db_password, $db_name);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
- Write an SQL SELECT query and use LIKE operator in WHERE clause.
- LIKE operator will allow us to fetch the most relevant record that matches our search query.
- For example, if the user enters “h”, all the records containing “h” in the title or author name will be fetched.
<?php
if (!empty($_POST['search'])) {
$Search_Query = $conn->real_escape_string($_POST['search']);
$query = "SELECT * FROM books
WHERE title LIKE '%{$Search_Query}%' OR author LIKE '%{$Search_Query}%' LIMIT 6 OFFSET 0; ";
$result = $conn->query($query) or die($conn->error);
$html = "<table class='table table-bordered'>";
$html .= "
<tr class='bg-primary'>
<th>Id </th>
<th>Title </th>
<th>Author</th>
<th>Description</th>
</tr>
";
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_assoc($result)) {
$html .= "<tr><td>" . $row['id'] . "</td>";
$html .= "<td>" . $row['title'] . "</td>";
$html .= "<td>" . $row['author'] . "</td>";
$html .= "<td>" . $row['description'] . "</td></tr>";
}
$html .= "</table>";
echo $html;
} else {
echo "Sorry! no records found";
}
} else {
echo "Please! Search a query.";
}
$conn->close();
output of the code:
We have successfully discussed every aspect of ajax live data search using PHP, jquery ajax and mysql. Now let’s end the article here. You can use your creativity to use this concept in a lot of variations. I hope I have conveyed the base to ajax live search and now you would be able to mold it according to your scenario and requirements. Thanks for reading and supporting us.