Php and mysql query infinite subtree output-PHP source code

The example of php's query of the infinite subtree output with mysql is actually an infinite classification. Here I have provided you with several examples of the infinite php classification. I hope this will help you. The example of php's query of the infinite subtree output with mysql is actually an infinite classification. Here I have provided you with several examples of the infinite php classification. I hope this will help you.

Script ec (2); script

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 the function, pass in the parameter, and continue the query
$ Arr [0] ['children '] [$ key] ['name'] = $ value ['username']; // combines 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 a top-level ID
Echo 'var data = '. json_encode ($ list );

This is the output Array and transfer it to json. This tutorial is provided by the cenxi website! Test

Example

Table Structure: The id field is the category id, the name field is the category name, The father_id field is the id of the parent category, and the path field is the category path (the set of the ancestor of the category ), isdir is used to determine whether it is a directory (1 is yes, 0 is no ).

Display function:

The Code is as follows: // $ count indicates the 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. = "example-". $ rs [$ I] [name];
}
Else
{
$ Name. = "example-". $ rs [$ I] [name];
}
If ($ rs [$ I] [isdir])
{
$ Str. = "". $ name ."";
}
Else
{
$ Str. = $ name ";
}
$ Temp = $ count 1;
$ Str = $ this-> sort_list ($ str, $ rs [$ I] [id], $ temp );
$ I;
}
Return $ str;
}

$ This-> the SQL object is an SQL operation object, the re_datas () function returns the queried array, and the SQL _numrows () function returns the queried number.

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

Example

Table: category
Id int primary key, auto-Increment
Name varchar category name
Pid int parent class id. The default value is 0.

Pid of top-level classification is 0 by default. When we want to retrieve the subcategory tree of a specific category, the basic idea is recursion. Of course, we do not recommend that you query the database every recursion due to efficiency issues, the common practice is to extract all categories and save them to the PHP array for processing. Finally, the results can be cached to improve the efficiency of the next request.

First, construct an original array, which can be pulled directly from the database:

$ Categories = array (
Array ('id' => 1, 'name' => 'pc', 'pid '=> 0 ),
Array ('id' => 2, 'name' => 'cell phone ', 'pid' => 0 ),
Array ('id' => 3, 'name' => 'notebook ', 'pid' => 1 ),
Array ('id' => 4, 'name' => 'desktop ', 'pid' => 1 ),
Array ('id' => 5, 'name' => 'Smartphone ', 'pid' => 2 ),
Array ('id' => 6, 'name' => 'function', 'pid '=> 2 ),
Array ('id' => 7, 'name' => 'superscript', 'pid '=> 3 ),
Array ('id' => 8, 'name' => 'gamely', 'pid' => 3 ),
); The goal is to convert it to the following structure:

Computer
-Notebook
--- Superscript
--- Game book
-Desktop
Mobile phone
-Smart machines
-Function machine

If it is represented by an array, you can add a children key to store its subcategories:

Array (
// 1 corresponds to the id to facilitate direct reading
1 => array (
'Id' => 1,
'Name' => 'computer ',
'Pid '=> 0,
Children => array (
& Array (
'Id' => 3,
'Name' => 'notebook ',
'Pid '=> 1,
'Children '=> array (
// Omitted here
)
),
& Array (
'Id' => 4,
'Name' => 'desktops ',
'Pid '=> 1,
'Children '=> array (
// Omitted here
)
),
)
),
// Other categories are omitted
) Processing process:

$ Tree = array ();
// Step 1: Use 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 is to add each category to the children array of the parent class by reference, so that a tree structure can be formed after a traversal.
Foreach ($ tree as $ k => $ item ){
If ($ item ['pid']! = 0 ){
$ Tree [$ item ['pid'] ['children '] [] = & $ tree [$ k];
}
}
Print_r ($ tree); the output is as follows:

Array
(
[1] => Array
(
[Id] => 1
[Name] => Computer
[Pid] => 0
[Children] => Array
(
[0] => Array
(
[Id] => 3
[Name] => notebook
[Pid] => 1
[Children] => Array
(
[0] => Array
(
[Id] => 7
[Name] => superscript
[Pid] => 3
[Children] => Array
(
)
)
[1] => Array
(
[Id] => 8
[Name] => game book
[Pid] => 3
[Children] => Array
(
)
)
)
)
[1] => Array
(
[Id] => 4
[Name] => Desktop
[Pid] => 1
[Children] => Array
(
)
)
)
)
[2] => Array
(
[Id] => 2
[Name] => mobile phone
[Pid] => 0
[Children] => Array
(
[0] => Array
(
[Id] => 5
[Name] => smart machine
[Pid] => 2
[Children] => Array
(
)
)
[1] => Array
(
[Id] => 6
[Name] => function machine
[Pid] => 2
[Children] => Array
(
)
)
)
)
[3] => Array
(
[Id] => 3
[Name] => notebook
[Pid] => 1
[Children] => Array
(
[0] => Array
(
[Id] => 7
[Name] => superscript
[Pid] => 3
[Children] => Array
(
)
)
[1] => Array
(
[Id] => 8
[Name] => game book
[Pid] => 3
[Children] => Array
(
)
)
)
)
[4] => Array
(
[Id] => 4
[Name] => Desktop
[Pid] => 1
[Children] => Array
(
)
)
[5] => Array
(
[Id] => 5
[Name] => smart machine
[Pid] => 2
[Children] => Array
(
)
)
[6] => Array
(
[Id] => 6
[Name] => function machine
[Pid] => 2
[Children] => Array
(
)
)
[7] => Array
(
[Id] => 7
[Name] => superscript
[Pid] => 3
[Children] => Array
(
)
)
[8] => Array
(
[Id] => 8
[Name] => game book
[Pid] => 3
[Children] => Array
(
)
)
) Advantage: the relationship is clear and easy to modify the upper-and lower-level relationship.

Disadvantage: PHP is used for processing. If the number of classes is large, the efficiency will be reduced.