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