Modeling an N-Dimensional Catalog

An N-dimensional product catalog, then between a catalog with subcategories of unlimited nesting, The idea is such that a product can have a minimum of one, and a maximum of N (Well, for starters, N is at least 5, but the idea is to make it NOT limited) and if pure category (pid=0) you can create either a product or a subcategory. If we create a subcategory, then the id of the parent category (genus, cat,) is written in the pid field of this newly created subcategory, and from now on, only subcategories can be created in the genus, cat (products are no longer possible), until all subcategories. And if we create a product in the genus, cat, then in the product table, the link_id field of the created product is assigned the id genus, cat, and in it we can now create only products until all products are deleted from the table.

So far, the standard scheme of a 3-level catalog, but the trick is that the pid field of the category table is not INT, but VARCHAR, and when we create K3 in the 2nd level (K2) category, then pid is written comma-separated id genus, cat , starting from the topmost, Tomezh if in K1 with id=1 pid=0, created K2 with id=2 and pid=1, and created K3 with id=3 in it, then its pid will be equal to pid=' 1,2' and t,d,

Here is a simplified table structure with a test dump:

1. CREATE TABLE `test`
2. (
3. `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
4. `pid` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
5. `title` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ''
6. )
7. ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

1. INSERT INTO `test`
2. (`id`,`pid`,`title`)
3. VALUES
4. (1,'0','Cat 1'),
5. (2,'0','Cat 2'),
6. (3,'0','Cat 3'),
7. (4,'1','Cat 1.1'),
8. (5,'1','Cat 1.2'),
9. (6,'1','Cat 1.3'),
10. (7,'1,4','Cat 1.1.1'),
11. (8,'1,4','Cat 1.1.2'),
12. (9,'1,4','Cat 1.1.3'),
13. (10,'1,5','Cat 1.2.1'),
14. (11,'1,5','Cat 1.2.2'),
15. (12,'1,5','Cat 1.2.3'),
16. (13,'1,6','Cat 1.3.1'),
17. (14,'1,6','Cat 1.3.2'),
18. (15,'1,6','Cat 1.3.3'),
19. (16,'2','Cat 2.1'),
20. (17,'2','Cat 2.2'),
21. (18,'2','Cat 2.3'),
22. (19,'0','Cat 4');

In order to find out which category is the last (having no children, therefore either having products or empty), and which is the parent (having children, therefore having subcategories) We need to separate the "Last id" group (3,7,8,9, 10,11,12,13,14,15,16,17,18,19) which are either empty or contain products, and a group of "Intermediate id" (1,2,4,5,6)

To do this, we create a query that would count the number of child categories, and thus we can easily separate the Intermediate subcategories from the final ones.

For the admin part:

1. list_cat($pid)
2. {
3. if ((int)$pid <= 0) $pid = 0;
4. $query = 'SELECT t1.*,COUNT(t2.pid)col FROM '.$this->table.'_cat t1 LEFT JOIN '.$this->table.'_cat t2 ON FIND_IN_SET(t1.id,t2. pid) WHERE t1.pid=\''.addslashes($pid).'\' GROUP BY t1.id ORDER BY t1.order';
5. $res = $this->db->assoc($query);
6. return (is_array($res) && count($res) > 0) ?$res :false;
7. }

Where the division into "Final" and "Intermediate" categories occurs by counting child subcategories.

For cat 1 pid='0' (exactly '0', not NULL) And for all subsequent children to the corresponding pid

And the client side:

1. list_cat($id)
2. {
3. $query = 'SELECT t1.*,COUNT(t2.pid)col FROM '.$this->table.'_cat t1 LEFT JOIN '.$this->table.'_cat t2 ON FIND_IN_SET(t1.id,t2. pid) WHERE (t1.pid=\''.(int)$id.'\' OR t1.pid LIKE \'%,'.(int)$id.'\') AND t1.status>0 GROUP BY t1.id ORDER BY t1.order';
4. $res = $this->db->assoc($query);
5. return (is_array($res) && count($res) > 0) ?$res :false;
6. }

Where, unlike the admin part, only viewing is performed, and therefore we do not need to know the parent's pid in full and we can cheat with the LIKE operator to select not the entire pid, but only its numeric ending, which makes it possible to apply CNC to links (when we make a selection not by the 1st id, but by the id bundle separated by commas, we cannot replace it via .htaccess)