Example of unlimited classification in PHP (imitation Taobao product classification)

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 Add several top-level categories

INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '0', 'mobile phone');
INSERT INTO `type` (`id`, `fid `, `name`) VALUES (NULL, '0', 'computer');
INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '0', 'shoes') ;
INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '0', 'Clothes');

Here fid=0 represents the top category

Then add several subcategories for {computer}

INSERT INTO `type` (`id`, `fid`, `name`) VALUES (NULL, '2', 'Desktop'), (NULL, '2', 'Notebook');

Here fid=2, the id 2 is the id of the category {Computer}, if you add the subcategory of {Shoes}, then fid=3 In the same way, if you add a subcategory to {notebook}, fid=6

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

3. Delete If you want to delete the category {Notebook}, you can do this:

DELETE FROM `type` WHERE `id`=6

{Notebook} subcategories must also be processed accordingly.

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']);//Recursion
 }
}
del(6);//Perform operation

Why is it so troublesome to use recursion instead of deleting it directly like this?

DELETE FROM `type` WHERE `fid`=6

So you can directly delete {ausu}, {hp}? But assuming {ausu} has a subcategory {a1}, and {a1} also has a subcategory {a2}, the data cannot be completely deleted without recursion.

3. Search

1. Find 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'] );//Recursion
 }
}
sel(2);

IV. Practical application Add a field `tid` to the data table, the field value is the id of the `type` table to which the record belongs. It must be id and not name, because the value of name may change.

For example, query products belonging to the {Computer} category:

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

That’s it, I hope it will be helpful to everyone.