An in depth understanding of the php infinite classification

Infinite classification is a data structure commonly used in practical development.
Title: similar to taobao category of goods, can be set in any category of its subclasses.

1. Create the 'type' table
` id ` from growth
'fid' int(11) defaults to (0), the parent node id
'name' varchar(50), classification name

CREATE TABLE `type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
)

2. Add
Let's start by adding a few top-level categories

INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '0', ' Mobile phone ');
INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '0', ' The computer ');
INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '0', ' shoes ');
INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '0', ' clothes ');

Here fid=0 is the top-level category

Next we add several subcategories for {computer}

INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '2', ' desktop '), (NULL, '2', ' The notebook ');

Here fid= 2,2 this id is id for categorizing {computer}, and fid=3 for adding {shoes} subcategories
Similarly, we add subcategories for {notebook}, fid=6

INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '6', 'ausu'), (NULL, '6', 'hp');

3. Delete
If we want to delete the {notebook} category, it's easy

DELETE FROM `type` WHERE `id`=6

We also need to remember to deal with the subcategories of {notebook}

function del($fid) {
    $sql="SELECT * FROM `type` WHERE `fid`=$fid";
    $rs=mysql_query($sql);

    for ($i = 0; $i < count($rs); $i++) {
        $sql="DELETE FROM `type` WHERE `id`={$rs[$i]['id']}";
        mysql_query($sql);

        del($rs['id']);// recursive 
    }
}
del(6);// Perform operations 

Now you might wonder why you bother with recursion instead of just deleting it like this

DELETE FROM `type` WHERE `fid`=6

So we can just delete {ausu}, {hp}? But assuming that {ausu} has a subclass {a1} and {a1} has a subclass {a2}, we cannot completely delete the data without recursion.

3. Look for
1. Find the subcategories of {computer}

SELECT * FROM `type` WHERE `fid`=2

2. Find all subcategories of {computer}

function sel($fid) {
    $sql="SELECT * FROM `type` WHERE `fid`=$fid";
    $rs=mysql_query($sql);

    for ($i = 0; $i < count($rs); $i++) {
        echo $rs[$i]['name'];

        sel($rs[$i]['id']);// recursive 
    }
}
sel(2);

4. Actual data application
Add a field 'tid' to the data table, whose value is id of the table to which the record belongs, 'type' table. Must be id and cannot be name, as the value of name may change.
For example, search for items belonging to the {computer} category

SELECT * FROM `goods` WHERE `tid`=2

Note: Errors may occur if the code is not run, but the idea is correct. The main thing is to understand the tree structure, not to remember the code.