In this tutorial, you will learn how to build a dynamic search functionality for your website that allows users to find the information they need quickly. We will use PHP as the server-side language and MySQL as the database to store and retrieve data. By following this tutorial, you will learn how to create a search box, filter data from the database based on user input, and display the results in an organized manner. This tutorial is a great starting point for creating a search box and filtering data from a database in PHP and MySQL.
Here are the steps on how to do it;
1. Make a search box.
Create an HTML code for the search bar.
<form action="userdisplay-blog-search.php" method="post">
<div class="input-group">
<input type="text" class="form-control" style="width: 85%" name="valueToSearch" id="" placeholder="Search...">
<button class="form-control" style="width: 15%" name="search" title="Search"><i class="bi bi-search"></i></button>
</div>
</form>For this search bar, I used HTML and Bootstrap for the style.
You can do or use your design for the search bar, but do not forget to include the name on its input tag or textbox. In my sample search bar, I used name=”valueToSearch” for the input tag and name=”search” for the button tag.
2. Write the PHP code to search and filter data from the database.
For example, here, I have a database table called tb_blogs. The table has a total of eight records.
And here’s the web page that displays those blogs.
Here are the PHP codes to search and filter data from that table and display it on your page.
if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT * FROM tb_blogs WHERE CONCAT(`blog_id`, `title`, `blog`) LIKE '%".$valueToSearch."%'";
$search_result = filterTable($query);
}
else {
$query = "SELECT * FROM tb_blogs";
$search_result = filterTable($query);
}This code checks if a form has been submitted by checking if the “search” key in the $_POST array is set. If the form has been submitted, it retrieves the value entered by the user, builds a query to search for the value in all columns of the “tb_blogs” table, and executes the query. If the form has not been submitted, the script builds a query to retrieve all records from the “tb_blogs” table. The result of the query is stored in the “$search_result” variable.
// function to connect and execute the query
function filterTable($query)
{
include '../php/dbconnect.php';
$filter_Result = mysqli_query($conn, $query);
return $filter_Result;
}The “filterTable” function is used to execute the query. The function takes a query string as an argument and executes the query using the mysqli_query function. Before executing the query, the function includes the “dbconnect.php” file, which establishes a connection to the database. The result of the query is stored in the “$filter_Result” variable and returned by the function.
if (mysqli_num_rows($search_result) == 0) {
echo "<p>No result</p>;
}This code checks if the number of rows in the result of the query is equal to zero. If the number of rows is zero, it means that no records were found in the query and the code outputs an HTML div element with an appropriate message. The div element contains a header element with an icon and a message indicating that no results were found.
while ($row = mysqli_fetch_assoc($search_result))
{
$category=$row['category'];
$title=$row['title'];
$blog=$row['blog'];
$datetime=$row['datetime'];
$uni_id=$row['blog_id'];
}This is a while loop that fetches one row of the result set at a time and moves the internal pointer to the next row until there are no more rows. The mysqli_fetch_assoc the function retrieves the result set as an associative array, where each column name serves as the key to the corresponding value.
Inside the loop, the code assigns the values of each column of the current row to respective variables ($category, $title, $blog, $datetime, and $uni_id).
.
And here’s the code for the whole section of our PHP search bar.
<!--Second Section to display blogs-->
<section id="blogs" class="mt-2 overflow-auto" style="height: 74vh;">
<div class="row g-1" style="width: 99%">
<!--Select all from tb_blogs to display-->
<?php
if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
// search in all table columns
// using concat mysql function
$query = "SELECT * FROM tb_blogs WHERE CONCAT(`blog_id`, `title`, `blog`) LIKE '%".$valueToSearch."%'";
$search_result = filterTable($query);
}
else {
$query = "SELECT * FROM tb_blogs";
$search_result = filterTable($query);
}
// function to connect and execute the query
function filterTable($query)
{
include '../php/dbconnect.php';
$filter_Result = mysqli_query($conn, $query);
return $filter_Result;
}
if (mysqli_num_rows($search_result) == 0) {
echo "<p>No result</p>;
}
while ($row = mysqli_fetch_assoc($search_result))
{
$category=$row['category'];
$title=$row['title'];
$blog=$row['blog'];
$datetime=$row['datetime'];
$uni_id=$row['blog_id'];
?>
<div class="col-4">
<div class="card">
<div class="card-header fw-bold" style="font-size: .8rem;">
<?php echo $category; ?>
</div>
<div class="card-body" style="height: 300px;">
<h5 class="card-title"><?php echo $title; ?></h5>
<p class="sentence" style="font-size: .9rem;"><?php echo $string; ?></p>
<a href="userdisplay-blog-view.php?blog_id=<?php echo $uni_id; ?>" class="text-dark">Read More...</a>
<p class="text-muted m-1" style="font-size: .8rem;"><?php echo $datetime; ?></p>
</div>
</div>
</div>
<?php
}
?>
</div>
</section>If the user entered a specific value in the search bar and click the search button. It will display all the blogs that have that value.
For example, the user searches the word/letter “py”, when he/she clicks the search button it will filter all the data that includes that word.
Result;
In conclusion, building a search box and filtering data from a database in PHP and MySQL is a valuable skill to have for any web developer. The techniques covered in this tutorial provide a solid foundation for creating dynamic and user-friendly search functionality on your website. Whether you are looking to improve the user experience or make your website more efficient, implementing a search box and filter system is a great way to achieve both. We hope that this tutorial has been helpful and informative and that you have gained a deeper understanding of how to create a search box and filter data from a database in PHP and MySQL.