If you need to filter data with checkboxes in PHP & MySQL for your web application. Then This tutorial will be very helpful for you.
In this tutorial, the checkbox filter data source code is shared with some simple steps that will be very easy to implement into the project.
Now let’s how works –
Some categories will be displayed from the database with checkboxes. When you check a checkbox category then the data will display in tabular form based on that checked checkboxes. If you check multiple checkboxes, then also, data will be filtered for all checked checkboxes.
How to Filter Data with Checkbox using PHP, MySQL
Now, Let’s start the coding to filter data by category with the following simple steps –
Learn Also –
Filter Data by Category in PHP & MySQL
Filter Prices From Low to High in PHP & MySQL
TO DO List using PHP and MySQL
1. Create a Directory Structure
First of all, You should create the following directory structure to filter data by category.
source-code/ |__database.php |__index.php |__filter-data.php |__display-data.php |__get-category.php |
2. Create MySQL Table
Now, Create a MySQL database
CREATE DATABASE codingstatus;
Create a table with the name of the products
CREATE TABLE `products` ( `id` int(10) NOT NULL AUTO_INCREMENT, `productName` varchar(255) DEFAULT NOT NULL, `price` int(20) DEFAULT NOT NULL );
4. Insert Data into MySQL Table
Now, You should insert some records into the MySQL Table. This data will be used to display for the checkboxes and display in the HTML table.
INSERT INTO products(id, productName, category) VALUES (1', 'product-1','category1'), (2', 'product-2','category2'), (3', 'product-3', 'category2'), (4', 'product-4', 'category1'), (5', 'product-5', 'category3'), (6', 'product-6', 'category3'), (7', 'product-7', 'category1'), (8', 'product-8', 'category1'), (9', 'product-9', 'category4'), ('10', 'product-10', 'category3');
3. Setup MySQL Database Connection
Now, You have to connect your PHP to the MySQL database with the following PHP script –
In this script, we have declared the table name in the constant variable to use it globally on any page.
define('productTable', 'products');
File Name – database.php
<?php define('productTable', 'products'); $hostname = "localhost"; $username = "root"; $password = ""; $databasename = "codingstatus"; $conn = mysqli_connect($hostname, $username, $password, $databasename); if (!$conn) { die("Unable to Connect database: " . mysqli_connect_error()); } ?>
Get Category From the Database
To display categories dynamically for the checkboxes from the database, You need to write the code according to the following points
- Create a user-defined function getCategory() and write code within it according to the next all steps
- make $conn global to use it anywhere in the file
- create a variable $data with an empty array.
- Write SQLi Query to fetch unique categories from the database
- If the category rows are greater than zero then fetch the category with the $result->fetch_all(MYSQLI_ASSOC) and store it in the variable $data
- At last return the $data to get categories based on filtered category
File Name – get-category.php
<?php function getCategory() { global $conn; $data =[]; $query = "SELECT distinct category FROM ".productTable; $result = $conn->query($query); if($result->num_rows > 0) { $data = $result->fetch_all(MYSQLI_ASSOC);; } return $data; }
5. Create a Form to Filter Data
Now, You need to create the price filtering UI by using the following points –
- Create a basic HTML structure/code
- Include the database.php, filter-data.php & get-category.php files that will be explained in the next steps.
- Make a form with the post method.
- Call the function getCategory and store into the $getCategories
- print the value of category for the checkbox by applying foreac loop on the $getCategories
- Create a submit button with the name of filter
- Include the file display-data.php just after the form.
File Name – index.php
<?php include("database.php"); include("filter-data.php"); include("get-category.php"); ?> <form method="post"> <?php $getCategories = getCategory(); foreach($getCategories as $category) { $checked = ''; if(isset($_POST['category']) && in_array($category['category'], $_POST['category'])){ $checked = "checked"; } ?> <input type="checkbox" value="<?php echo $category['category']; ?>" name="category[]" <?php echo $checked; ?>> <?php echo $category['category']; ?> <?php } ?> <input type="submit" name="filter"> </form> <?php include("display-filtered-data.php"); ?>
6. Write Filtering data with a checkbox Script in PHP
To write filtering data with a checkbox, You need to follow the following steps –
- Start writing code, If the form is submitted then call the function filterDataByCategory() and store in the $filterDataByCategory.
- Create a user-defined function filterDataByCategory() and write the code according to the next steps.
- Get the checkbox value using $_POST[‘category’] and store it in the $filterByCategory.
- $filterByCategory stores an array of values. So, convert it into the string using implode()
- Declare $conn with the keyword global to use it globally.
- If $filterByCategory is not empty then write code according to the next points
- Write MySQLi query to select productName & category
- If te filtered rows are greater than zero then fetch it with the $result->fetch_all(MYSQLI_ASSOC) and store in the $data.
- After that, return the value of $data to display records in the datable.
File Name – filter-data
<?php if(isset($_POST['filter'])) { $filterDataByCategory = filterDataByCategory(); } function filterDataByCategory() { $filterByCategory = $_POST['category']; $categories = "'".implode("', '", $filterByCategory)."'"; global $conn; $data =[]; if(!empty($filterByCategory)){ $query = "SELECT productName, category FROM ".productTable; $query .= " WHERE category IN ($categories)"; $result = $conn->query($query); if($result->num_rows > 0) { $data = $result->fetch_all(MYSQLI_ASSOC);; } } return $data; }
7. Display Filtered Data in Table
To display the filtered data in the HTML table, configure the following points –
- If the $filterDataByCategory has values then write code within it from the next all points.
- Create the first row of the table with three columns S.N, Product, & category
- Then create other rows dynamically based on the filtered data with checkbox
- print the value of columns ‘productName’ and ‘category’ respectively using foreach on the filterDataByCategory.
File Name – display-data.php
<?php if(!empty($filterDataByCategory)) { ?> <table border="1" cellspacing="0" cellpadding="5"> <tr> <th>S.N</th> <th>Product</th> <th>Category</th> </tr> <?php foreach($filterDataByCategory as $filterData){ $sn = 1; ?> <tr> <td><?php echo $sn; ?></td> <td><?php echo $filterData['productName']?></td> <td><?php echo $filterData['category']?></td> </tr> <?php $sn++; } ?> </table> <?php } ?>
8. Test yourself to filter data by Category
After implementing all the given steps, Now you can test the PHP filtering data by checkbox to open in the web browser.
You will get a select input field with some categories options
- When you select a category option and submit the form then the records will display in an HTML table related to the filtered category.