Php+mysql query to realize infinite subordinate classification tree output example _php skill

The example of this article describes the Php+mysql query implementation of infinite subordinate classification tree output. Share to everyone for your reference, specific as follows:

Here introduced PHP combined with MySQL query infinite subordinate tree output, in fact, is an infinite classification. I'll give you a few examples of PHP infinite classification.

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 parameters, continue querying subordinate
     $arr [0][' Children ' [$key] [' name ']= $value [' username ']; Combined Array
      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 the output Array and then transferred to JSON

Example:

Table structure: ID field for category ID, Name field for category name, father_id field for id,path field of parent category as category path, store the collection of ancestors of the taxonomy, Isdir to determine if it is a directory, 1 is, or No. 0.

Display function:

$count for classification rank
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;
}

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 for the top level category is 0 by default, when we want to take out a subcategory of a classification tree, the basic idea is recursion, of course, for efficiency issues do not recommend each recursive query database, the usual practice is to first talk about all categories out, save to the PHP array, and then processing, Finally, the results can be cached to increase the efficiency of the next request.
First, build an original array, which is pulled directly from the database:

$categories = Array (
  ' id ' =>1, ' name ' => ' computer ', ' 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 ' => ' 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 Ben
-desktop
phone
-smart Machine
-function machine

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

Array (
  //1 corresponding ID, to facilitate direct reading of the
  1 => array (
    ' id ' =>1,
    ' name ' => ' computer ',
    ' pid ' =>0,
    Children=>array (
      &array (
        ' id ' =>3, '
        name ' => ' notebook ',
        ' pid ' =>1,
        ' children ') =>array (
          //omitted
        )
      ),
      &array (
        ' id ' =>4,
        ' name ' => ' desktop ',
        ' pid ' = >1,
        ' Children ' =>array (
          //omitted)),
      /
    /
  other classification omitted
)

Processing 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] => array ([ID] => 1 [name] => computer [PID] => 0 [children] => Array
              ([0] => Array ([id] => 3 [name] => Notebook
                    [PID] => 1 [children] => Array ([0] => Array
                      ([ID] => 7 [name] => super [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] => cell phone [PID] => 0 [Childre 
              N] => 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] =&gt ; Array ([id] => 7 [name] => super [PID] => 3 [C
 Hildren] => Array               ()) [1] => Array ([id] => 8
                [Name] => game [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] => [PID] => 2 [children] => Array ()) [7] => Array ([ ID] => 7 [name] => super [PID] => 3 [children] => Array ()) [8] => 
    Array ([ID] => 8 [name] => game this [PID] => 3 [children] => 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.

For more information about PHP interested readers can view the site topics: "Php+mysql database operations to get Started", "PHP basic Grammar Introductory Course", "PHP Operations and Operator Usage Summary", "PHP object-oriented Program Design Introductory Course", "PHP Network Programming Skills Summary", " PHP array Operation tips Encyclopedia, PHP string (String) Usage summary and PHP Common database operation Skills Summary

I hope this article will help you with the PHP program design.