[Asp] the implementation of simple algorithm and code of infinite classification http://bbs.blueidea.com/thread-1982151-1-1.html

I. Preface
In many cases, the second-level classification cannot meet the requirements, but the examples of multi-level classification available on the Internet are difficult to find, so this article is available.

Http://bbs.blueidea.com/viewthread.php? Tid = 1182243

Let's take a look at this first. It introduces a super good algorithm. I don't know much about it.

2. Problems to be Solved:
1. classification algorithms are often represented by Tree Representation and traversal. So, what fields should I have if I use a table in the database to express tree classification?
2. How to quickly restore a tree from this table;
3. Determine whether a category is a subclass of another category;
4. How to find all products under a certain category;
5. How to generate the path of the category.
6. How to add a category;

Iii. Advantages and Disadvantages of Recursive Implementation
How can we implement multi-level classification?
It is estimated that the first thought is recursion, which is easy to implement. Adding, modifying, and deleting nodes under a specified node (that is, classification, the same below) is not a problem,
In addition, it is not difficult to implement node movement, but you must note that the parent node of the target node cannot be the parent node of the current node (equal to or not moved ), it cannot be a subnode of the current node (similar to the window folder, a folder cannot be moved to its own word folder ).
However, the most worrying thing is to search for things under a specified node. What should I do? That is, question 3 above. Remember, this is to include all sub-nodes. Do we still need to recursive them?

4. Introduce my simple algorithms (I used, not invented)
Take a common commodity system as an example.
4.1 table structure
[1] category table, t_sort, table structure 1. Sortpath stores the Node path, which is a key point.
[2] item table, t_product, table structure 2.
[Center] Figure 1
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open (this. SRC);} "alt =" "src =" http://mytju.com/temp/tech/t_sort.gif "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

Figure 2
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open (this. SRC);} "alt =" "src =" http://mytju.com/temp/tech/t_product.gif "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>
[/Center]
4.2 brief Algorithm Description
[1] parentid stores the parent node of a node. If parentid of a node is 0, it is considered as a level-1 classification.
[2] A node's sortpath is its parent node's sortpath + its own sortid + ",". For example, if the parent node of A sortid = 32 is node 21 and the sortpath of node 21 is ",", the sortpath of node 32 is ", 32 ,". Look at figure 3. You may not be able to figure out why you need multiple commas (,) at last. You will understand them later. The two left sides of sortpath of all nodes are "0," because they are all under the root node. A node's sortpath must be included in its sortpath.
[Center]
Figure 3
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open (this. SRC);} "alt =" "src =" http://mytju.com/temp/tech/t_sort2.gif "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0> [/center]
4.3 code highlights.
The following describes the functions to be implemented.
[1] adding nodes
<1> select the parent node, which can be the root node or all the lower-level nodes (it is better to list a tree menu for the user to choose, don't worry, you can implement). In fact, you can select parentid.
<2> If parentid is 0, the parent sortpath is "0,". If parentid is <> 0, the t_sort table obtains the parent sortpath Based on parentid.
<3> Add a record to t_sort. sortpath = higher-level sortpath + new record sortid + ",".
<4> sample code is shown in Figure 4 and figure 5. Among them, norecord, closers (), showmsg (), and closeconn () are all functions or sub defined by me. Their functions are as the name suggests, so I will not talk about them. Note that if you use ms SQL, the code is slightly different. I am also surprised that, in ms SQL, after addnew, the new automatic number can be output, but there is no connection with character 1. Please tell us why.
[Center]
Figure 4
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open (this. SRC);} "alt =" "src =" http://mytju.com/temp/tech/addsort.gif "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0>

Figure 5
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open (this. SRC);} "alt =" "src =" http://mytju.com/temp/tech/addsort_mssql.gif "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0> [/center]
[2] modifying nodes
The attribute of a node has only one name. You can simply update the node and leave it alone.
[3] deleting a node
<1> select a node
<2> If parentid is 0, an error is returned. The root node cannot be deleted.
<3> Delete the node and all its subnodes. You may wonder if it is very troublesome. Haha, I only use one SQL statement.

[Copy to clipboard] [-]

Code:

(ACCESS) SQL = "delete from t_sort where instr (sortpath, '," & parentid & ",')> 0"

[Copy to clipboard] [-]

Code:

(Ms SQL) SQL = "delete from t_sort where charindex ('," & parentid & ",', sortpath)> 0"

The essence of this algorithm is here. Think about it carefully. The last comma of sortpath is also here.
<4> Delete products under all the preceding nodes. Same as above, the table name is different.

[Copy to clipboard] [-]

Code:

(ACCESS) SQL = "delete from t_product where instr (sortpath, '," & parentid & ",')> 0"

[Copy to clipboard] [-]

Code:

(Ms SQL) SQL = "delete from t_product where charindex ('," & parentid & ",', sortpath)> 0"

<5> the sample code is shown in Figure 6. Ms SQL code will not be pasted.
[Center]
Figure 6
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open (this. SRC);} "alt =" "src =" http://mytju.com/temp/tech/delsort.gif "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0> [/center]
[4] mobile nodes
Difficult: Open your eyes and take a closer look.
<1> select the node parentid to be moved and the target node toparentid (that is, the owner of the current node ).
<2> If parentid = 0, the root node cannot be moved.
<3> If toparentid = parentid, you need to put yourself under your own account and report an error.
<4> get its sortpath Based on parentid. We call it frompath.
<5> If toparentid = 0, topath = "0,". If toparentid <> 0, obtain its sortpath and call it topath.
<6> If toparentid is equal to the parent node of the node to be moved, an error is returned. The determination method is to check whether topath & parentid & "," is equal to frompath.
<7> If toparentid is a subnode of the node to be moved and cannot be moved, an error is returned. The method is to check whether instr (topath, frompath) is greater than 0.
<8> combine the new sortpath of the node to be moved, that is, newpath = topath & parentid &",".
<9> Update sortpath () of the node to be moved and all its subnodes (). For example, "," Move to ",", the new sortpath is ", 5," (Think About It, right ). And the left half of all sub-nodes of "," is ",", you only need to replace ", 5," with ", 5, "That's all.

[Copy to clipboard] [-]

Code:

(ACCESS) SQL = "Update t_sort set sortpath = '" & newpath & "' + mid (sortpath, Len ('" & frompath & "') + 1) where instr (sortpath, '"& frompath &"')> 0"

[Copy to clipboard] [-]

Code:

(Ms SQL) SQL = "Update t_sort set sortpath = Replace (sortpath, '" & frompath & "', '" & newpath &"') where charindex [('"& frompath &"', sortpath)> 0"

Because access does not seem to have a built-in repalce function, it is troublesome.
<10> Update the parentid of the node to be moved. Update directly.
<11> the product follows the classification, so the parentid of the product does not need to be updated. You only need to update its sortpath. The statement is the same as <9>, but the table name is changed to t_product.
<12> the sample code is shown in figure 7. The ms SQL code is only the above two SQL statements are different, not pasted.
[Center]
Figure 7
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "style =" width: 735px; cursor: hand; height: 738px "onclick =" If (! This. resized) {return true;} else {window. Open (this. SRC);} "Height = 738 alt =" Click here to open new window
CTRL + mouse wheel to zoom in/out "src =" http://mytju.com/temp/tech/movesort.gif "width = 716 onload =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0 resized =" true "> [/center]
[5] foreground product category browsing
Generally, the front-end does not list all categories at once. They are classified and viewed layer by layer. All we need to do is to list its subcategories when browsing a category.
<1> getting the direct subcategory is easy.

[Copy to clipboard] [-]

Code:

SQL = "select sortid, sortname from t_sort where parentid =" & sortid

That's all.
<2> generally, a current location is displayed, that is, the path of the category. What should we do? Do you want to perform recursive queries? Of course not. I used a tip here.
When browsing a category, we will have a sortid, which can be used to retrieve sortpath from t_sort... if you don't want to talk about it, let's look at the Demo code.
The sample code is shown in figure 8.
[Center]
Figure 8
Screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. style. cursor = 'hand'; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "onclick =" If (! This. resized) {return true;} else {window. open (this. SRC);} "alt =" "src =" http://mytju.com/temp/tech/browersort.gif "onLoad =" If (this. width> screen. width * 0.7) {This. resized = true; this. width = screen. width * 0.7; this. alt = 'click here to open new window \ nctrl + mouse wheel to zoom in/out';} "border = 0> [/center]
Used for display

[Copy to clipboard] [-]

Code:

<% For I = 1 to ubound (myarray)
Response. Write "-& gt; <a href = 'product. asp? Sortid = "& myarray (I) &" '> "& getvaluebyid (myarray (I), namearray) &" </a>"
Next %>

OK. Getvaluebyid is a function I wrote. See later.
<3> display all products under this category and all its subcategories.

[Copy to clipboard] [-]

Code:

SQL = "select * From t_product where instr (sortpath, '," & sortid & ",')> 0"

If only products under this category are displayed, you can use parentid to determine.
[6] product search on the frontend
If your search form does not contain a product category, there is nothing special. If there is a product category, it is also very easy to add a where condition for SQL

[Copy to clipboard] [-]

Code:

"And instr (sortpath, '," & sortid & ",')> 0"

That's all.
[7] add and modify background Products
To add a sortid, You need to select its category. In this way, you can obtain sortid, obtain its sortpath, and save it to the product record.
The modification is similar.
[8] background product Deletion
It is not related to the category. You can delete it directly based on productid.
5. Additional information
Currently, no code has been split up for everyone (too troublesome). The main things are on top.
Master leads the door and practices are personal.

[Copy to clipboard] [-]

Code:

'----- Obtain the sub ----------------- by ID -----------------
Function getvaluebyid (sortid, inarray)
Dim I
If not isarray (inarray) then
Getvaluebyid = ""
Exit Function
End if
For I = 0 to ubound (inarray, 2)
If CSTR (sortid) = CSTR (inarray (0, I) then
Getvaluebyid = inarray (1, I) 'returns name
Exit Function
End if
Next
Getvaluebyid = ""
End Function