Click a category to list all products of the current category and all its subcategories.

Click a category to list all products of the current category and all its sub-categories. The product table is the cate_id of the associated category table to determine the category of the product.

Now I want to click a category to list all products of the current category and all its subcategories.

Please kindly advise

All the headers are broken. recursion? Or? How can this problem be solved?

Reply to discussion (solution)

After clicking the category, you will know the classification number.
Query the database. if the parent class number is the classification class, it is its subcategory.

After clicking the category, you will know the classification number.
Query the database. if the parent class number is the classification class, it is its subcategory.

I don't want subcategories. I want products under all subcategories.

It's strange. do you still have to worry about the products under the sub-category with the sub-classification number?

I don't know if your data volume is large.
After obtaining the parent category ID for the small business site, find all the sub-category IDs and merge them together: 2, 5, 6, 7, 8.
Go to the SQL statement of the product table and use the in keyword to find out how to use it. search for it...
I don't know if in can use indexes?

If the data volume is large, use redundancy for efficiency.
Add a floor field to the category table. for the parent category, write level 1 and Level 1 sub-categories and write level 2 and Level 2 sub-categories...
Suppose you have three layers of classification, add three fields in the Product Table, cate, cate1, cate2
Which sub-category does the product belong to fills up three fields from parent to child 2
When filtering, find the floor of the category based on the input cateid, and then find all its parent category IDs.
The rest is to filter all the IDs as conditions.
The key here is to create a BTREE index, which is included in the order of cate to cateN.
Method 2 of the same data volume is much faster, but its disadvantage is that the product table needs to be changed when the sub-category is moved.

It's strange. do you still have to worry about the products under the sub-category with the sub-classification number?

This also involves subcategories. subcategories are all descendant classes, so the first thing I think of is recursion.

It would be easy to use a single subcategory, but the number of subcategories is unknown for an Infinitus classification.

I thought of a solution.

I don't know if your data volume is large.
After obtaining the parent category ID for the small business site, find all the sub-category IDs and merge them together: 2, 5, 6, 7, 8.
Go to the SQL statement of the product table and use the in keyword to find out how to use it. search for it...
I don't know if in can use indexes?

If the data volume is large, use redundancy for efficiency.
Add a floor field to the category table. for the parent category, write level 1 and Level 1 sub-categories and write level 2 and Level 2 sub-categories...
Suppose you have three layers of classification, add three fields in the Product Table, cate, cate1, cate2
Which sub-category does the product belong to fills up three fields from parent to child 2
When filtering, find the floor of the category based on the input cateid, and then find all its parent category IDs.
The rest is to filter all the IDs as conditions.
The key here is to create a BTREE index, which is included in the order of cate to cateN.
Method 2 of the same data volume is much faster, but its disadvantage is that the product table needs to be changed when the sub-category is moved.

I am using an Infinitus classification, and the sub-classification levels are not certain. There may be sub-categories under the sub-categories. so I first thought of recursion. later I thought I should start with database design, add a path field to the category table.

If you retrieve all at the beginning, there is no such thing as "click a category and list all products of the current category and all its subcategories ".
Since it is click, it is naturally a level-1 subcategory, and then click again. This is a unified style.

That is, you can obtain all the data at a time. it is also a level-1 read indicated by the parent category.

There is no need for recursion. you can go to the "hierarchy" field in the above table to find the classification type of the clicked category. All are descendant categories.
For example, if the ID of the clicked category is 18, you can use cate_path like "% 18-%" or cate_path like "%-18%" to filter.

You did not say such a key word as the limit!

I think the idea is still the second method on the 4th floor. all the parent category IDs of the cateid to be queried are also pushed out and merged to query them together.
Yes, or-yes.
The cateid field of the product is saved in the following format: 1, 5, 8, and 21 are classified from level 1 to level N.
If you want to query all the products whose cateid is 8, first find the two upper-level categories 5 and 1, and generate the strings, 8
Then, the query statement uses cateid like '$ cateid %'. of course, this field is separately indexed.

Supplement

If you want to query all the products whose cateid is 8, first find the two upper-level categories 5 and 1, and generate the strings, 8

The strings in this step can be written in the database, or the query strings corresponding to all category IDs can be cached. the actual running time can be skipped ^

I think there will be problems with your filtering method. What if the id is 8? Expressions like "% 8%" include 18, 81, 82, and so on.

Just change the expression in this method. If the id is 0, it may also be clicked. in addition to the search results, the last in contains an id of the current click. Otherwise, you do not need to add anything else, because only 0 will not bring "-" before and after.

You did not say such a key word as the limit!

I think the idea is still the second method on the 4th floor. all the parent category IDs of the cateid to be queried are also pushed out and merged to query them together.
Yes, or-yes.
The cateid field of the product is saved in the following format: 1, 5, 8, and 21 are classified from level 1 to level N.
If you want to query all the products whose cateid is 8, first find the two upper-level categories 5 and 1, and generate the strings, 8
Then, the query statement uses cateid like '$ cateid %'. of course, this field is separately indexed.

There is no difference between "use" and "use", and the parent category does not need to be searched.

This is simple. Continue to query.

The subcategory is queried by the primary category ID, the third level is queried by the subcategory ID, and the fourth level is queried by the third level ID ......

Of course, your server will be able to withstand such a tough situation.

No matter whether you are an infinitely large number of queries, they all look like this. There are no shortcuts.

This is simple. Continue to query.

The subcategory is queried by the primary category ID, the third level is queried by the subcategory ID, and the fourth level is queried by the third level ID ......

Of course, your server will be able to withstand such a tough situation.

No matter whether you are an infinitely large number of queries, they all look like this. There are no shortcuts.

He has a cate_path field. this is not required.

Just change the expression in this method. If the id is 0, it may also be clicked. in addition to the search results, the last in contains an id of the current click. Otherwise, you do not need to add anything else, because only 0 will not bring "-" before and after.

What the middleware thought was really comprehensive and 180 included 18, which really meant to be leveraged.

I have no time to look at it.
What you are talking about is adding path to the category and then querying the SQL statement of the product using in?
Will it be more efficient to use in for an infinite number of classes?
Fortunately, indexes can be used in. I don't know if mysql is sorted in. Otherwise, the index tree needs to be traversed from the beginning to the end, and the complexity needs to be multiplied by the number of IDs.

On the 4th and 10th floors, I spoke about the accelerated query solution.
How can we achieve this?
After obtaining the cateid, read its classification path from the memory cache (generated at one time when the category is edited) using the php variable hash, which is almost time-consuming.
Then, the path string is used to locate all datasets at a time using the index tree, which is fast.

Sorry, the weather is hot and impetuous. please correct me if you have any mistakes.