CodingStatus

- Level Up Your Status with Coding Expertise!

Filter Data with Checkboxes in PHP & MySQL

By

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.

Filed Under: Uncategorized