Filter Data with Checkboxes in PHP & MySQL

by

If you’re looking to filter data with checkboxes in PHP and MySQL for your web application, this tutorial provides a straightforward source code with easy implementation steps.

Here’s how it works:

Categories with checkboxes will be shown from the database. When you check a category checkbox, data related to that category will be displayed in a table. If you check multiple checkboxes, the data will be filtered accordingly for all the checked categories

Steps 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.

coding/
   |__database.php
   |__index.php
   |__filter-data.php
   |__display-data.php
   |__get-category.php
   |

2. Create a 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,
 `category` 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 –

File Name – database.php

<?php

$host = "localhost";
$user = "root";
$password = "";
$database = "codingstatus";

$conn = new mysqli($host, $user, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Get Category From the Database

To get a category from the database, write the given code in the get-category.php file

The code defines a PHP function, getCategory, that retrieves distinct category data from a presumed database connection. It constructs an SQL query to select distinct categories from the “products” table, executes the query, and returns the resulting data as an associative array.

File Name – get-category.php

<?php

function getCategory() {

    global $conn;
    $data =[];
    $query = "SELECT distinct category FROM products;
    $result = $conn->query($query);

    if($result->num_rows > 0) {
        $data = $result->fetch_all(MYSQLI_ASSOC);;
        
    } 
   return $data;
}

Steps to write code:-

  1. Begin by defining a PHP function called getCategory.
  2. Use the global keyword to access the presumed global variable $conn, assumed to be a database connection.
  3. Initialize an empty array named $data to store the category data retrieved from the database.
  4. Construct an SQL query to select distinct categories from the “products” table.
  5. Execute the query using $conn->query($query) and store the result in the $result variable.
  6. Check if rows are in the result by evaluating $result->num_rows > 0.
  7. If there are rows, fetch all rows as an associative array using $result->fetch_all(MYSQLI_ASSOC) and assign it to the $data variable.
  8. Return the populated $data array, containing distinct category data from the “products” table in the database.

5. Create a Form to Filter Data

This PHP code sets up a form with checkboxes for categories retrieved from a database. Users can select categories, and submit the form, and the selected categories are processed to filter and display corresponding data using external files for database access and data display.

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");
 ?>

Steps to write code –

  1. Include three external files: “database.php,” “filter-data.php,” and “get-category.php” to execute necessary functionalities in your PHP code.
  2. Define a form using the HTML <form> tag with the method attribute set to “post.”
  3. Call the getCategory() function to retrieve an array of categories and iterate through each category using a foreach loop.
  4. Generate a checkbox input for each category within the loop, setting its value to the category name, and pre-check the checkbox if the category is selected in the submitted form.
  5. Determine checkbox states by checking if the category is set in the $_POST data and using the in_array function.
  6. Include the “display-filtered-data.php” file, which presumably contains code to handle and display the filtered data based on the selected categories.
  7. Include a submit button in the form with the name attribute set to “filter.”
  8. Implement a filtering mechanism, allowing users to select categories using checkboxes, submit the form, and view filtered data based on their selections.

6. Filter Data by Category

This PHP code, triggered by the submission of a form, filters product data based on selected categories. It constructs a SQL query to retrieve product names and categories from the “products” table, using the selected categories from the form. The filtered data is then returned as an associative array,

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;
}

Steps to write code:-

  1. Check if the “filter” button is set in the $_POST data, and if true, invoke the filterDataByCategory() function.
  2. In the filterDataByCategory() function, retrieve the selected categories from the submitted form data and construct a comma-separated string of category names.
  3. Access the global database connection variable, $conn.
  4. Initialize an empty array, $data, to store the filtered product data.
  5. Check if there are selected categories, then construct and execute an SQL query to select product names and categories from the “products” table where the category is in the list of selected categories.
  6. If the query results in rows, fetch the data into the $data array as an associative array.
  7. Return the filtered data, if any, from the function.

7. Display Filtered Data in Table

Display filtered data in a  table with serial numbers, product names, and categories for filtered product data if the data is not empty.

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
}

?>

Steps to write code –

  1. Display a table with borders, spacing, and cell padding for filtered product data if $filterDataByCategory is not empty.
  2. Set up table headers for serial number, product name, and category.
  3. Use a for-each loop to iterate through each item in $filterDataByCategory, initializing a serial number counter.
  4. For each item, create a table row displaying the serial number, product name, and category.
  5. Increment the serial number within the loop to ensure proper numbering.
  6. Close the table after the loop if $filterDataByCategory is not empty, completing the display of filtered product data.

8. Test yourself to filter data by Category

After implementing all the given steps, Now you can test the PHP  filtering data by using the checkbox to open in the web browser.

You will get a select input field with some category options

  • When you select a category option and submit the form, the records will display in an HTML table related to the filtered category.