In this blog, we will learn how to create a blog about Adding Nested Categories using PHP and MySQL. Creating a Nested Category in PHP allows you to organize data hierarchically, where categories can have subcategories, forming a parent-child relationship. In this blog, we will explore how to build a dynamic system using PHP and MySQL to add and display nested categories. By implementing recursion, we can efficiently handle multiple levels of categories and subcategories, providing a flexible and scalable solution. Join us as we step through the process of creating a robust nested category system that can be adapted to various applications and enhance data organization.
How to add nested categories, we will see it step by step below
Table of Contents
1. Create a Database
First, you create a nestedcategory Database in your PHPMyAdmin
2. Create a Table
create a table named categories
CREATE TABLE `categories` (
`id` int(11) NOT NULL,
`parent_id` int(11) NOT NULL,
`category_name` varchar(100) DEFAULT NULL,
`is_active` tinyint(1) NOT NULL,
`created_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The categories the table is defined as follows:
Explanation:
The “categories” table has the following columns:
- “
id” A unique identifier for each category (primary key). - “
parent_id” The ID of the parent category. If the category is a top-level category, theparent_idis set to 0. - “
category_name” The name of the category. - “
is_active” A flag to indicate if the category is active (1) or inactive (0). - “
created_at” The date and time when the category was created.
Overall, the code and database structure allows users to add new categories with parent-child relationships, creating a nested category system. The user can select an existing category as the parent when adding a new category, enabling the creation of subcategories.
3. Database Connection
create config.php file. The config.php file contains the necessary code to establish a connection to the MySQL database. It sets up variables for the servername, username, password, and database name. The mysqli_connect function is used to create a connection to the MySQL server, and the connection is checked for errors using mysqli_connect_error().
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "nestedcategory";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $db);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>4. Create Index File
Create an Index.php file.
<?php
include 'connection.php';
if (isset($_POST['submit'])) {
$sql = "INSERT INTO categories (parent_id, category_name) VALUES ('".$_POST['parentcat']."','".$_POST['catname']."')";
$result = mysqli_query($conn, $sql);
if ($result) {
echo 'insert';
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Add Nested Category</title>
</head>
<body>
<h1>Add Nested Category in PHP | Category Subcategory using PHP & MYSQL</h1>
<form action="" method="post">
<label for="">Parent Category</label>
<select name="parentcat" id="">
<option>Select Category</option>
<option value="0">None</option>
<?php
$sqldata = "SELECT * FROM categories";
$sresult = mysqli_query($conn, $sqldata);
while ($cat = mysqli_fetch_array($sresult)) {
?>
<option value="<?php echo $cat['id']; ?>"><?php echo $cat['category_name']; ?></option>
<?php
}
?>
</select>
<label for="">Category Name</label>
<input type="text" name="catname" id="">
<input type="submit" name="submit" id="">
</form>
</body>
</html>
Explanation:
- The “
index.php” file is the main page that displays the HTML form to add a new category. - The
include "connection.php";line includes the “config.php” file that establishes the database connection. - When the form is submitted (when the submit button is clicked), the PHP code checks for the form submission using the”
isset($_POST['submit'])“. - If the form is submitted, the PHP code constructs an SQL query “(
$sql)” to insert the new category into the “categories” table. It uses the values from the form “($_POST['parentcat']” and “$_POST['catname'])” to set theparent_idandcategory_namefields. - The “
mysqli_query()” function is used to execute the SQL query, and the result is stored in$result. - If the query is successful, the text “insert” is echoed as feedback.
How to Display a Nested Category in PHP
This PHP code displays nested categories in a hierarchical structure. It uses a recursive function called show category to traverse through the categories and their parent-child relationships. Let’s break down the code step-by-step:
Database Connection
Add connection.php
include 'connection.php';
This line includes the connection.php file that establishes the database connection. It provides access to the $conn variable, which is the database connection object used in the mysqli_query function.
Recursive Function (showcategory):
function showcategory($parentid)
{
$sql = "select * from categories where parent_id='".$parentid."'";
$result = mysqli_query($GLOBALS['conn'],$sql);
$output ="<ul>\n";
while($data=mysqli_fetch_array($result))
{
$output.="<li>\n".$data['name'];
$output.=showcategory($data['id']);
$output.="</li>";
}
$output.="</ul>";
return $output;
}Explanation:
- The “
showcategory” function is a recursive function that takes a parameter “$parentid“, which represents the ID of the parent category whose subcategories need to be displayed. - The function first constructs an SQL query to select all categories where the “
parent_id” matches the given “$parentid“. - The “
mysqli_query” function is used to execute the SQL query, and the result is stored in “$result“. - The function initializes an empty string “
$output” to store the HTML representation of the nested categories as an unordered list (“<ul>“). - Inside a “
while“ loop, the function fetches each row from the result set using “mysqli_fetch_array“, and for each category, it adds a list item (<li>) to$outputwith the category name (“$data['name']“). - The function then calls itself recursively, passing the current category’s ID (“
$data['id']“) as the new “$parentid“. This recursive call is essential for displaying subcategories under their respective parent categories. - After processing all categories, the function adds the closing unordered list tag (“
</ul>“) to “$output“. - Finally, the function returns the complete HTML representation of the nested categories.
HTML Output (index.php):
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>category</title>
</head>
<body>
<h1>Nested Category in PHP</h1>
<?php echo showcategory(0); ?>
</body>
</html>
Explanation:
- The HTML page (“
index.php“) starts with the usual HTML boilerplate. - The page displays a heading with the text “Nested Category in PHP.“
- Inside the body, the PHP code “
<?php echo showcategory(0); ?>” is used to call the “showcategory” function and display the nested categories. The parameter0is passed to start displaying categories from the top-level (categories “parent_id” equal to 0).
When you run this PHP script, it will generate an HTML page displaying nested categories in a hierarchical manner, with subcategories listed under their respective parent categories. The function uses recursion to traverse through the category tree and construct the nested structure.
Full Code Example
<?php
include'connection.php';
function showcategory($parentid)
{
$sql = "select * from categories where parent_id='".$parentid."'";
$result = mysqli_query($GLOBALS['conn'],$sql);
$output ="<ul>\n";
while($data=mysqli_fetch_array($result))
{
$output.="<li>\n".$data['name'];
$output.=showcategory($data['id']);
$output.="</li>";
}
$output.="</ul>";
return $output;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>category</title>
</head>
<body>
<h1>Nested Category in PHP</h1>
<?php echo showcategory(0); ?>
</body>
</html>
