METANIT.COM

Filter Data by Category in PHP & MySQL

If you need to filter data by category in PHP & MySQL for your web application. Then This tutorial will be very helpful for you.

In this tutorial, the PHP filter data with category source code is shared with some simple steps that will be very easy to implement into the project.

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 –

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');

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

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.

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 .

 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

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.

Related posts:

Hey there, Welcome to CodingStatus. My Name is Md Nurullah from Bihar, India. I’m a Software Engineer. I have been working in the Web Technology field for 4 years. Here, I blog about Web Development & Designing. Even I help developers to build the best Web Applications.

Источник

Filtering table in php

Подобно тому, как мы получаем все данные из таблицы, мы можем применять фильтрацию выбирать данные по определенному критерию. Для фильтрации команде SELECT передается выражение WHERE, которая принимает названия столбцов их значения в качестве критерия фильтрации. Например, получение всех данные из таблицы Users, где id = 1 :

SELECT * FROM Users WHERE >Однако если данные для фильтрации приходят извне, нам необходимо учитывать их потенциальную опасность. Рассмотрим получение данных на примере таблицы Users, созданной в прошлых, которая имеет следующее определение:

CREATE TABLE Users (id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INTEGER)

Объектно-ориентированный стиль

Например, мы хотим получать в GET-запросе значение для id и по нему получать из базы данных нужные данные. Определим для этого следующий скрипт user.php :

     connect_error)< die("Ошибка: " . $conn->connect_error); > $userid = $conn->real_escape_string($_GET["id"]); $sql = "SELECT * FROM Users WHERE "; if($result = $conn->query($sql))< if($result->num_rows > 0) < foreach($result as $row)< $username = $row["name"]; $userage = $row["age"]; echo "
Информация о пользователе
 
Имя: $username
 
Возраст: $userage
 
"; > > else< echo "
Пользователь не найден
"; > $result->free(); > else< echo "Ошибка: " . $conn->error; > $conn->close(); > ?>  

Здесь через запрос GET получаем параметр id — идентификатор пользователя, который надо получить из базы данных. Однако поскольку это значение приходит извне, к нему применяется метод real_escape_string() , который экранирует спецсимволы:

$userid = $conn->real_escape_string($_GET["id"]);

Для упрощения обращения к скрипту определим скрипт index.php , который будет выводить список пользователей с ссылкой на скрипт user.php :

     
Список пользователей
 connect_error)< die("Ошибка: " . $conn->connect_error); > $sql = "SELECT id, name FROM Users"; if($result = $conn->query($sql))< echo ""; foreach($result as $row)< echo ""; echo ""; echo ""; echo ""; > $result->free(); > else< echo "Ошибка: " . $conn->error; > $conn->close(); echo "
Имя
 
" . $row["name"] . "
Подробнее
"; ?>  

Таким образом, при обращении к скрипту index.php браузер отобразит список пользователей, а нажав на ссылку рядом с каждым пользователем, мы перейдем к подробному описанию:

Процедурный стиль

      $userid = mysqli_real_escape_string($conn, $_GET["id"]); $sql = "SELECT * FROM Users WHERE "; if($result = mysqli_query($conn, $sql)) < if(mysqli_num_rows($result) >0) < foreach($result as $row)< $username = $row["name"]; $userage = $row["age"]; echo "
Информация о пользователе
 
Имя: $username
 
Возраст: $userage
 
"; > > else< echo "
Пользователь не найден
"; > mysqli_free_result($result); > else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); > ?>  

     
Список пользователей
 $sql = "SELECT id, name FROM Users"; if($result = mysqli_query($conn, $sql))< echo ""; foreach($result as $row)< echo ""; echo ""; echo ""; echo ""; > echo "
Имя
 
" . $row["name"] . "
Подробнее
"; mysqli_free_result($result); > else < echo "Ошибка: " . mysqli_error($conn); >mysqli_close($conn); ?>  

Источник