Home > Database > Mysql Tutorial > How to Get All Child and Descendant Nodes for a Parent in MySQL Using PHP?

How to Get All Child and Descendant Nodes for a Parent in MySQL Using PHP?

Barbara Streisand
Release: 2024-11-07 02:13:02
Original
714 people have browsed it

How to Get All Child and Descendant Nodes for a Parent in MySQL Using PHP?

Get all child, grandchild, etc. nodes under parent using PHP with MySQL query results

This article will guide you through a method to retrieve all child and descendant nodes for a specified parent using PHP with MySQL query results. This technique eliminates the need to construct a multidimensional array of the tree structure and enables you to obtain all nodes directly.

Consider a MySQL table organized as an adjacency list, where hierarchy data is represented with columns for id, name, and parent_id. The following SQL query can retrieve all rows from this table into an associative array:

$r = mysql_query("SELECT * FROM test ");
$data = array();
while($row = mysql_fetch_assoc($r)) {
  $data[] = $row;
}
Copy after login

Let's assume we want to filter this array for nodes under a parent with id 3, including the parent itself. This custom fetch_recursive function can accomplish this:

function fetch_recursive($src_arr, $currentid, $parentfound = false, $cats = array()) {
  foreach($src_arr as $row) {
    if((!$parentfound && $row['id'] == $currentid) || $row['parent_id'] == $currentid) {
      $rowdata = array();
      foreach($row as $k => $v)
        $rowdata[$k] = $v;
      $cats[] = $rowdata;
      if($row['parent_id'] == $currentid)
        $cats = array_merge($cats, fetch_recursive($src_arr, $row['id'], true));
    }
  }
  return $cats;
}
Copy after login

To use this function, pass the $data array obtained from the query as the first argument and the id of the parent you want to retrieve the child nodes for as the second argument:

$list = fetch_recursive($data, 3);
Copy after login

$list will now contain an associative array with all child nodes and the parent node (id 3) included.

This solution efficiently retrieves all child and descendant nodes for a specified parent without the need for multidimensional tree structure construction.

The above is the detailed content of How to Get All Child and Descendant Nodes for a Parent in MySQL Using PHP?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template