deleting nested categories from a table

I have a table called categories which looks like this

+-------------+--------------+---------------+
|  id         |  catName     |  parentId     |
+-------------+--------------+---------------+
|  1          | Category 1   |  0            |
|  2          | Category 2   |  0            |
|  3          | Sub Cat 1    |  1            |
|  4          | Sub Cat 2    |  1            |
|  5          | Sub sub cat 1|  4            |
|  6          | Sub sub cat 2|  4            |
|  7          | Category 2   |  0            |
+-------------+--------------+---------------+

It would be easy to delete a category and its direct child:

mysql_query("DELETE FROM `categories` WHERE `id`='$id'");
mysql_query("DELETE FROM `categories` WHERE `parentId`='$id'");

However I need to be able to delete ALL children of a category. For e开发者_JAVA百科xample if Category 1 was deleted Sub Cat 1, Sub Cat 2, Sub sub cat 1, Sub sub cat 2 will be deleted

Any help appreciated.

You could use foreign keys, a very nice feature of mysql. This is a kind of integrity check for referencing different relations. If you create a foreign key, mysql ensures that referenced entries exist, for example. During the creation of a foreign key, you can define what mysql should do if the "parent element" is deleted. You can specify "SET NULL", "UPDATE" or "DELETE CASCADE". This means, if you delete a category, every connected sub category is deleted as well. And because of the fact that every sub category is the parent category of a sub sub category, those are deleted as well.

Here is a simple recursive script for deleting your category and all the nested categories nested inside.

try {

$db = new PDO(DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASS);
}catch( PDOException $e ) {
    echo $e->getMessage();
}



class Category {



function delete_category($parrent){
    global $db;
    $stmt = $db->prepare("SELECT `id` FROM `categories` WHERE `parrentId`=:parrentId");
    $stmt->execute(array('parrentId'=>$parrent));
    while($category = $stmt->fetchObject('Category')){
        $category->delete_category($category->id);
    }
        $stmt = $db->prepare("DELETE FROM `category` WHERE `id` = :id");
        $stmt->execute(array('id'=>$parrent));

}



function __construct(array $data = NULL) {
    if( empty($data) )
    return;

    foreach( $data as $field => $value ) {
        $this->$field = $value;
    }
}}

Where $db is your PDO object for connecting with the database. It may have some bugs, as i just modified mine to suit your problem. Hope this helps.

PS: for calling your function you just do: $category=new Category(); $category->delete_category($_GET['id'])

Quoted my original answer for clarity.

UPDATE 1: The problem with the table splitting is that it isn't clear that there is a fixed number of sub, sub, categories. For this to work, you would need to split into as many tables as there are subcategories, each with foreign keys back to their parent ids. Not practical if the number of sub (or sub sub) categories is dynamic.

This is a situation where I think you would benefit in changing your table design. If you split the two tables into categories and subcategories you could take advantage of the foreign key functionality as described by strauberry. For example (these don't look like tables, but hopefully this makes sense) Stuff in parentheses is explanation:

categories

• id (primary key)
• catName

subcategories

• sub_id (primary key)
• id (foreign key referencing primary key of categories table)
• subCatName

Then when you create the categories table, you can use the ON DELETE CASCADE option. This will mean that whenever you delete a category from the categories table, all related subcategories in the subcategories table will be deleted automatically for you by MySQL. Powerful, and reduces your code, and you don't have to make sure all deletions happen in the right tables manually. Hope that clarifies a little.

UPDATE 2: The problem with this option is, well, it won't work :) MySQL will not allow you to subquery on the same table that you are doing the DELETE on. So what appeared at first to be a simple problem... isn't.

If you can't change the table design then you could do the following (substitute the id you actually want to delete for category_id_to_delete):

DELETE FROM categories
WHERE id = category_id_to_delete OR id IN (
    SELECT id
    FROM categories
    WHERE parentID = category_id_to_delete
)

So, I did some more checking, and came across this similar question on SO MySQL: How to find all IDs of children recursively?, and the highest rated answer points to this article Managing Hierarchical Data in MySQL. That article points out the type of queries with LEFT JOINS that you need to use to select data, but doesn't explicitly cover how to delete that data. The recommended solution would be to use the queries related to adjacent sets from the article to select all the id information you need, and then in your code (php I think?) loop over those ids and construct a new query to delete them.

For example, if you are trying to delete Category 1 (id=1), your goal by looping over the ids from the LEFT JOIN query from the article would be to create another query that looks like:

DELETE FROM categories
WHERE id IN (1,3,4,5,6);

The code in this forum post also may help you as a means of doing it recursively in php: Adjacency List Model recursive deletion

I know that isn't a neat and tidy answer, but perhaps this will point you in the right direction.

demonstration of the mysql "cascade on delete" feature. if you run this in your console, then you can just run all the queries at once, and the output will make sense. if you run it in some graphical tool, then i suppose you'll have to run the queries one by one.

CREATE DATABASE `recursivedemo`;

USE `recursivedemo`;

CREATE TABLE `categories` (
    `id` INT AUTO_INCREMENT,
    `catName` VARCHAR(50),
    `parentId` INT,

    PRIMARY KEY (`id`),

    KEY `parentId` (`parentId`),
    CONSTRAINT `categories_fk_self` FOREIGN KEY (`parentId`) REFERENCES `categories`(`id`) ON DELETE CASCADE
) Engine=InnoDB;

SELECT * FROM `categories`;

INSERT INTO `categories`(`catName`, `parentId`) VALUES('Category 1', NULL);
INSERT INTO `categories`(`catName`, `parentId`) VALUES('Category 2', NULL);
INSERT INTO `categories`(`catName`, `parentId`) VALUES('Sub Cat 1.1', 1);
INSERT INTO `categories`(`catName`, `parentId`) VALUES('Sub Cat 1.2', 1);
INSERT INTO `categories`(`catName`, `parentId`) VALUES('Sub sub cat 1.2.1', 4);
INSERT INTO `categories`(`catName`, `parentId`) VALUES('Sub sub cat 1.2.2', 4);
INSERT INTO `categories`(`catName`, `parentId`) VALUES('Sub Cat 2.1', 2);
INSERT INTO `categories`(`catName`, `parentId`) VALUES('Sub Cat 2.2', 2);
INSERT INTO `categories`(`catName`, `parentId`) VALUES('Category 3', NULL);

SELECT * FROM `categories`;

DELETE FROM `categories` WHERE `id`=1;

SELECT * FROM `categories`;

DROP DATABASE `recursivedemo`;

You can use following Function in model for deleted nested categories:

 public function delete_by_id($cat_id)
{
     $this->db->delete('Categories', ['cat_id' => $cat_id]);
            $q = $this->db->where('parent_id', $cat_id)->get('Categories');
        foreach( $q->result() as $Child ) {
            $this->delete_by_id($Child->cat_id);
        }
}