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;
}

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;
}

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);

$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.