Two methods for implementing tree structure

Two methods to implement the tree structure: 1. Recursion
Recursion refers to explicitly calling itself in a function.
Recursive tree structure is characterized by fast data writing speed and slow display speed (especially when there are many branches/layers of the tree ). Applicable to scenarios where a large amount of data is written and the tree structure is complex.
Data structure (take mysql as an example)

Code :--------------------------------------------------------------------------------
Create table 'tree1 '(
'Id' tinyint (3) unsigned not null auto_increment,
'Parentid' tinyint (3) unsigned not null default '0 ',
'Topic 'varchar (50) default NULL,
Primary key ('id '),
KEY 'parentid' ('parentid ')
) TYPE = MyISAM;

Insert into 'tree1 '('id', 'parentid', 'topic') VALUES
(1, 0, 'tree 1 '),
(2, 0, 'tree 2 '),
(3,0, 'tree 3 '),
(4,2, 'tree 2-1 '),
(5, 4, 'tree 2-1-1 '),
(6, 2, 'tree 2-2 '),
(7, 1, 'tree 1-1 '),
(8, 1, 'tree 1-2 '),
(9, 1, 'tree 1-3 '),
(, 'Tree 1-2-1 '),
(, 'Tree 1-1-1 '),
(, 'Tree 1-1-1-1 ');
--------------------------------------------------------------------------------


Field description
Id, id of the record
Parentid, the record's parent Record id (0 is the root record)
Topic, the title of the record

Display program

Sequence tree:

PHP code :--------------------------------------------------------------------------------

<?
/* Database connection */
Mysql_connect ();
Mysql_select_db ('tree ');

/* Recursive function displayed in the tree */
Function tree ($ parentid = 0 ){
/* Execute an SQL query to obtain the title and id of the record */
$ SQL = "select topic, id from tree1 where parentid = $ parentid order by id asc ";
$ Rs = mysql_query ($ SQL );
/* Indent */
Echo ("<ul> ");
While ($ ra = mysql_fetch_row ($ rs )){
/* Display record title */
Echo ('<li>'. $ ra [0]. '</li> ');
/* Recursive call */
Tree ($ ra [1]);
}
Echo ("</ul> ");
}
Tree ();
?>

--------------------------------------------------------------------------------


Reverse Sequence tree:

PHP code :--------------------------------------------------------------------------------

<?
/* Database connection */
Mysql_connect ();
Mysql_select_db ('tree ');

/* Recursive function displayed in the tree */
Function tree ($ parentid = 0 ){
/* Execute an SQL query to obtain the title and id of the record */
$ SQL = "select topic, id from tree1 where parentid = $ parentid order by id desc ";
$ Rs = mysql_query ($ SQL );
/* Indent */
Echo ("<ul> ");
While ($ ra = mysql_fetch_row ($ rs )){
/* Display record title */
Echo ('<li>'. $ ra [0]. '</li> ');
/* Recursive call */
Tree ($ ra [1]);
}
Echo ("</ul> ");
}
Tree ();
?>

--------------------------------------------------------------------------------


Insert Data Program

PHP code :--------------------------------------------------------------------------------

<?
/* Database connection */
Mysql_connect ();
Mysql_select_db ('tree ');
$ SQL = "insert into tree (topic, parentid) values ('tree 3-1 ', 3 );";
Mysql_query ($ SQL );
?>

--------------------------------------------------------------------------------


2. Sort Field Method
This method is implemented by adding a field in the sequence position of the Flag record in the entire tree in the data structure. It features high display speed and efficiency. However, when the structure of a single tree is complex, the Data Writing efficiency is insufficient. In addition, the algorithm for inserting and deleting records is too complex in order, so records are usually arranged in reverse order.

Data structure (take mysql as an example)

Code :--------------------------------------------------------------------------------
Create table 'tree2 '(
'Id' tinyint (3) unsigned not null auto_increment,
'Parentid' tinyint (3) unsigned not null default '0 ',
'Rootid' tinyint (3) unsigned not null default '0 ',
'Lay' tinyint (3) unsigned not null default '0 ',
'Orders 'tinyint (3) unsigned not null default '0 ',
'Topic 'varchar (50) default NULL,
Primary key ('id '),
KEY 'parentid' ('parentid '),
KEY 'rootid' ('rootid ')
) TYPE = MyISAM

Insert into 'tree2' ('id', 'parentid', 'rootid', 'layer', 'Orders ', 'topic') VALUES
(, 0, 'tree 1 '),
(2,0, 2,0, 0, 'tree 2 '),
(3,0, 3,0, 0, 'tree 3 '),
(4,2, 2,1, 2, 'tree 2-1 '),
(5, 4, 2, 2, 3, 'tree 2-1-1 '),
(6, 2, 2, 1, 1, 'tree 2-2 '),
(, 4, 'tree 1-1 '),
(, 2, 'tree 1-2 '),
(, 1, 'tree 1-3 '),
(, 3, 'tree 1-2-1 '),
(, 5, 'tree 1-1-1 '),
(, 6, 'tree 1-1-1-1 ');
--------------------------------------------------------------------------------


Display program

PHP code :--------------------------------------------------------------------------------

<?
/* Database connection */
Mysql_connect ();
Mysql_select_db ('tree ');

/* Select all root record IDs */
$ SQL = "select id from tree2 where parentid = 0 order by id desc ";
$ Rs = mysql_query ($ SQL );
Echo ("<ul> ");
$ Lay = 0;
While ($ ra = mysql_fetch_row ($ rs )){
Echo ("<ul> ");
/* Select all records in the tree and sort them by the orders field */
$ SQL = "select topic, layer from tree2 where rootid = $ ra [0] order by orders ";
$ Rs1 = mysql_query ($ SQL );
While ($ ra1 = mysql_fetch_row ($ rs1 )){
/* Indent display */
If ($ ra1 [1]> $ lay ){
Echo (str_repeat ("<ul>", $ ra1 [1]-$ lay ));
} Elseif ($ ra1 [1] <$ lay ){
Echo (str_repeat ("</ul>", $ lay-$ ra1 [1]);
}
/* Record display */
// Echo ("$ ra1 [1]> $ lay ");
Echo ("<li> $ ra1 [0] </li> ");
$ Lay = $ ra1 [1];
}
Echo ("</ul> ");
}
Echo ("</ul> ");
?>

--------------------------------------------------------------------------------


Insert Data Program

PHP code :--------------------------------------------------------------------------------

<?
/* Database connection */
Mysql_connect ();
Mysql_select_db ('tree ');

/* Insert the root record */
$ SQL = "insert into tree2 (topic) values ('tree 5 ')";
Mysql_query ($ SQL );
$ SQL = "update tree2 set rootid = id where id =". mysql_insert_id ();
Mysql_query ($ SQL );

/* Insert sub-records */
$ Parentid = 5; // parent Record id
/* Retrieve the root Record id, parent record indent level, and parent record sequence position */
$ SQL = "select rootid, layer, orders from tree2 where id = $ parentid ";
List ($ rootid, $ layer, $ orders) = mysql_fetch_row (mysql_query ($ SQL ));
/* Update the orders value recorded after the insert position */
$ SQL = "update tree2 set orders = orders + 1 where orders> $ orders ";
Mysql_query ($ SQL );
/* Insert record */
$ SQL = "insert into tree2 (rootid, parentid, orders, layer, topic) values ($ rootid, $ parentid ,". ($ orders + 1 ). ",". ($ layer + 1 ). ", 'tree 2-1-2 ')";
Mysql_query ($ SQL);?>