Filter Data by Category in PHP & MySQL

by

Filtering data by category in PHP and MySQL involves retrieving data from a MySQL database based on a specific category selection. It will be very useful to filter table data of a specific category.

Now let’s how works –

You will get all the categories in the options of the select input field. When you select a category option then the data will display in tabular form based on the selected category.

Steps to Filter Data by Category in PHP, MySQL

Now, Let’s start the coding to filter data by category with the following simple steps –

Learn Also –

Filter Prices From Low to High in PHP & MySQL

TO DO List using PHP and MySQL

Delete Records with Multiple checkboxes using PHP & 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 have to insert some data records into the MySQL 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 in 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

If you need to display a category in the select box options from the database then you will have to implement the following steps –

  • Create a custom function wit the name of getCategory() and implement the next steps within it.
  • make $conn global to use it in anywhere.
  • create a $data and assign an empty array.
  • Write SQL Query to fetch data from the database
  • If the category records is greater than zero then get using $result->fetch_all(MYSQLI_ASSOC) and assign it to the $data
  • At last return the $data

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 two files database.php, filter-data.php & get-category.php
  • Create a form with the post method and create the select input field & submit button.
  • Call the function getCategory and assign to the $getCategories
  • apply for each on the $getCategories and print the value of category in the options
  • 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">
<select name="filterByCategory">
    <?php
      $getCategories = getCategory();
      foreach($getCategories as $category) {
    ?>
    <option value="<?php echo $category['category']; ?>" <?php echo $_POST['filterByCategory']==$category['category']?'selected':''; ?>>
     <?php echo $category['category']; ?>
    </option>
  <?php } ?>
</select>
<input type="submit" name="filter">
</form>


<?php
 include("display-filtered-data.php");
 ?>

6. Write Filtering data by Category Script in PHP

Now, create a price filtering script in PHP with the following points –

  • First, check whether the form is submitted or not to filter the price using $_POST[‘filter’]  within thin the if condition. if the form is submitted then call the function filterDataByCategory()  and assigned it to the $filterDataByCategory.
  • Create a custom function filterDataByCategory() and implement the following points within it
  • Get the filtered price input value using $_POST[‘filterByCategory’] and assign it to $filterPrice.
  • Declare $conn with the keyword global to use it globally.
  • If $filterByCategory is not empty then write the following points within the if condition.
  • Write MySQLi query to select data with a price based on the value of the category
  • If records are greater than zero then get the records with the $result->fetch_all(MYSQLI_ASSOC) and assign it to the $data.
  • After that, return the value of $data .

File Name – filter-data

<?php

if(isset($_POST['filter'])) {
    
    $filterDataByCategory = filterDataByCategory();

}

function filterDataByCategory() {

    $filterByCategory = $_POST['filterByCategory'];
    global $conn;
    
    $data =[];
    if(!empty($filterByCategory)){
    
        $query = "SELECT productName, category FROM ".productTable;
        $query .= " WHERE category= '$filterByCategory'";
     
        $result = $conn->query($query);
    
        if($result->num_rows > 0) {
          $data = $result->fetch_all(MYSQLI_ASSOC);;
        
        } 
   } 

   return $data;
}

 

7. Display Filtered Data in Table

Follow the following points to display data with the price in the HTML table when the filter price and submit the form.

  • If the value of $filterDataByCategory is not empty then implement the next all points within it
  • Create the first row of the table with three columns S.N, Product, price
  • Then create other rows dynamically based on the filtered category records.
  • Apply the foreach loop to the $filterDataByCategory and print the value of columns ‘productName’ and ‘category’ respectively.

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