PHP combines MySQL query infinite subordinate tree output

Tree output

function Get_array ($user _id, $top =0) {


Global $mysql, $_g;


$sql = "Select user_id as name from ' {spreads_users} ' where p1.spreads_userid= ' {$user _id} '";


$rows = $mysql->db_fetch_arrays ($sql);


if ($top ==1) {


$arr [0][' name ']= $user _id;


$arr [0][' Children ']=array ();


}


$top = $top +1;


foreach ($rows as $key => $value)


{


$r = Get_array ($value [' name ']); Call function, pass parameter, continue query subordinate


$arr [0][' Children '] [$key] [' name ']= $value [' username ']; Combining arrays


if (Is_array ($r)) {


$arr [0][' Children '] [$key] [' Children ']= $r [0][' Children '];


}





$i + +;


}








return $arr;


}


$list = Get_array ("1000", 1); Call function 1000 is the top-level ID


Echo ' var data= '. Json_encode ($list);





This is output Array and then transferred for JSON This tutorial is provided by Cenxi website development! Measured

Example

Table structure: The ID field is the category identifier, the Name field is the category name, the father_id field is the Id,path field for the parent category (the collection that stores the ancestor of the taxonomy), and Isdir determines whether it is a directory (1 is, 0 is no).

Display function:

The

Code is as follows://$count for classification level  
Sort_list ($str, $fatherid, $count)  
{ 
$rs = $this->sql->re_ Datas ("select * from sort where father_id = Fatherid"); 
$num = $this->sql->sql_numrows (); 
$i =0 ; 
$n = 1; 
while (Isset ($rs [$i]))  
{ 
$name = ""; 
for ($n = 1; $n < $count; $n)  
{ 
$name. = "│"; 
} 
if ($i 1== $num)  
{ 
$name. = "└─". $rs [$ i][name]; 
} 
else 
{ 
$name. = "├─". $rs [$i][name]; 
} 
if ($rs [$ I][isdir]  
{ 
$str. = "<span style= ' color: #CCCCCC ' > '. $name." </span> "; 
} 
else 
{ 
$str. = $name"; 
} 
$temp = $count 1; 
$str = $this->sort_list ($str, $rs [$i][id], $temp); 
$i; 
} 
return $str; &nbs P
}

Where the $this->sql object is the SQL Action class object, the Re_datas () function returns the array found, and the Sql_numrows () function returns the number of queries

Call method: $sort _list = sort_list ($sort _list,0,1);

Example

Table: Category
ID int primary KEY, self-increasing
Name varchar category names
PID int parent class ID, default 0

The PID of the top level classification is 0 by default. When we want to take out a subcategory of a taxonomy tree, the basic idea is recursion, of course, for efficiency issues do not recommend each recursive query the database, the usual practice is to first talk about all the sorting out, save to the PHP array, then processing, and finally can cache the results to improve the efficiency of the next request.

First, build an original array, which is pulled directly from the database:

$categories = Array (
Array (' ID ' =>1, ' name ' => ' computer ', ' pid ' =>0 '),
Array (' ID ' =>2, ' name ' => ' mobile ', ' pid ' =>0),
Array (' ID ' =>3, ' name ' => ' notebook ', ' pid ' =>1 '),
Array (' ID ' =>4, ' name ' => ' Desktop ', ' pid ' =>1),
Array (' ID ' =>5, ' name ' => ' smart machine ', ' pid ' =>2 '),
Array (' ID ' =>6, ' name ' => ' function machine ', ' pid ' =>2 '),
Array (' ID ' =>7, ' name ' => ' Super Ben ', ' pid ' =>3),
Array (' ID ' =>8, ' name ' => ' game book ', ' pid ' =>3 '),
); The goal is to convert it into the following structure

Computer
-Notebook
——-Super Ben
——-Game Book
-Desktop
Cell phone
-Intelligent Machine
-Function Machine

With an array, you can add a children key to store its subcategories:

Array


1 corresponding ID, easy to read directly


1 =&gt; Array (


' ID ' =&gt;1,


' Name ' =&gt; ' computer ',


' PID ' =&gt;0,


Children=&gt;array (


&amp;array (


' ID ' =&gt;3,


' Name ' =&gt; ' notebook ',


' PID ' =&gt;1,


' Children ' =&gt;array (


omitted here


)


),


&amp;array (


' ID ' =&gt;4,


' Name ' =&gt; ' Desktop ',


' PID ' =&gt;1,


' Children ' =&gt;array (


omitted here


)


),


)


),


Other classifications omitted


) Process:

$tree = Array ();
The first step is to take the category ID as the array key and create the children unit
foreach ($categories as $category) {
$tree [$category [' id ']] = $category;
$tree [$category [' id ']][' children '] = array ();
}
The second part, by using references, adds each taxonomy to the parent class children array, so that one traversal can form a tree structure.
foreach ($tree as $k => $item) {
if ($item [' pid ']!= 0) {
$tree [$item [' pid ']][' children '] = & $tree [$k];
}
}
Print_r ($tree); Print the results as follows:

Array


(


[1] =&gt; Array


(


[ID] =&gt; 1


[Name] =&gt; computer


[PID] =&gt; 0


[Children] =&gt; Array


(


[0] =&gt; Array


(


[ID] =&gt; 3


[Name] =&gt; Notebook


[PID] =&gt; 1


[Children] =&gt; Array


(


[0] =&gt; Array


(


[ID] =&gt; 7


[Name] =&gt; super Ben


[PID] =&gt; 3


[Children] =&gt; Array


(


)


)


[1] =&gt; Array


(


[ID] =&gt; 8


[Name] =&gt; game Book


[PID] =&gt; 3


[Children] =&gt; Array


(


)


)


)


)


[1] =&gt; Array


(


[ID] =&gt; 4


[Name] =&gt; desktop


[PID] =&gt; 1


[Children] =&gt; Array


(


)


)


)


)


[2] =&gt; Array


(


[ID] =&gt; 2


[Name] =&gt; mobile phone


[PID] =&gt; 0


[Children] =&gt; Array


(


[0] =&gt; Array


(


[ID] =&gt; 5


[Name] =&gt; Intelligent Machine


[PID] =&gt; 2


[Children] =&gt; Array


(


)


)


[1] =&gt; Array


(


[ID] =&gt; 6


[Name] =&gt; function machine


[PID] =&gt; 2


[Children] =&gt; Array


(


)


)


)


)


[3] =&gt; Array


(


[ID] =&gt; 3


[Name] =&gt; Notebook


[PID] =&gt; 1


[Children] =&gt; Array


(


[0] =&gt; Array


(


[ID] =&gt; 7


[Name] =&gt; super Ben


[PID] =&gt; 3


[Children] =&gt; Array


(


)


)


[1] =&gt; Array


(


[ID] =&gt; 8


[Name] =&gt; game Book


[PID] =&gt; 3


[Children] =&gt; Array


(


)


)


)


)


[4] =&gt; Array


(


[ID] =&gt; 4


[Name] =&gt; desktop


[PID] =&gt; 1


[Children] =&gt; Array


(


)


)


[5] =&gt; Array


(


[ID] =&gt; 5


[Name] =&gt; Intelligent Machine


[PID] =&gt; 2


[Children] =&gt; Array


(


)


)


[6] =&gt; Array


(


[ID] =&gt; 6


[Name] =&gt; function machine


[PID] =&gt; 2


[Children] =&gt; Array


(


)


)


[7] =&gt; Array


(


[ID] =&gt; 7


[Name] =&gt; super Ben


[PID] =&gt; 3


[Children] =&gt; Array


(


)


)


[8] =&gt; Array


(


[ID] =&gt; 8


[Name] =&gt; game Book


[PID] =&gt; 3


[Children] =&gt; Array


(


)


)


Advantages: Clear relationship, modify the relationship between the superior and subordinate is simple.

Disadvantages: Use PHP processing, if the number of large categories, efficiency will also be reduced.