Code implementation of infinite pole classification in MySQL

This article brings to you the content is about MySQL infinite pole classification code implementation, has certain reference value, has the need friend can refer to, hoped to be helpful to you.

I also find from other places, the analysis found quite good, special to share.

Unable to send diagram can only be processed simply:

ID name PID Path

1 News 0 0

2 China News 1 0-1

3 US News 1 0-1

4 Beijing News 2 0-1-2

5 Nail Brother News 3 0-1-3

A statement is done:

SELECT id,name,pid,path,concat (Path, '-', id) as NewPath from Cates ORDER by NewPath

Results after query:

ID name PID Path newpath

1 News 0 0 0-1

2 China News 1 0-1 0-1-2

4 Beijing News 2 0-1-2 0-1-2-4

3 US News 1 0-1 0-1-3

5 Nail Brother News 3 0-1-3 0-1-3-5

As you can see, the structure is already very clear. Very clever use of the MySQL sorting function, with the concat () function to connect the current path with the current ID (very want to concatenate strings), from the individual name NewPath, in the sort NewPath, skillfully is sorted first compare 0, in comparison 1, in comparison 2, in comparison 3 ..., So the results of the query is directly sorted by the previous level, the next only need to loop output in the foreground can be, "hierarchical relationship placeholder" also solve, NewPath 0-1-2-4 this is equivalent to infinite pole placeholder, PHP output to use the above statement query, in the background processing each piece of data NewPath field value, the value is divided into groups, in the statistics of the array, and then add a key in the array count (a set of data), the number of the statistics array into this key.

Example of thinkphp 5.0:

$list = Catemodel::fild (' Id,name,pid,path,concat (Path, '-', id) ')->order (' NewPath asc ')->select ();// The above model query method is just the approximate meaning, can be based on the top of the MySQL statement, with Tp5 way to spell out. foreach ($list as $k =>v) {$list [$k] [' count '] = count (Explode ('-', $v))}

This count is a placeholder for the hierarchy, and then loop it through the template, looping through the echoes several times  

<select name= "AA" >{volist name= "list" id= "VO"}<option vlaue= "{$vo. ID}" >{php}for ($i =0; $i < $vo [' count ' ]*2, $i + +) {echo ' &nbsp; '} {/php} {$vo .name}</option>{/volist}</select>

Effect:

News

China News

Beijing News

American News

Washington News