如何在多维数组中遵循父子关系？

Question

I have a classic database table for multi level categories like this (0 means root):

ID - Name - Father
1    News      0
2    Articles  0
3    Politics  1
4    Politics  2
5    World     3
6    World     4

I have a path like this: /News/Articles/Politics, and I have the above table data in an array.

I begin with an explode() call with / on the path, and I've tried a few methods, but nothing is good in all cases.

Here is something I've tried:

$cat_path_ary = explode($cfg['categories_separator'], $cat_path);
if (count($cat_path_ary) > 1) {
    $catname = array_pop($cat_path_ary);
    $catparent = array_pop($cat_path_ary);
    $cat_id = $this->getCatIDbyName($plugin, $catname, $catparent);
} else {
   $catname = $cat_path_ary[0];
   foreach($this->root_cats($plugin) as $categories) {
      if($categories['name'] == $catname ) {
       return $categories['cid'];
      }
   }
}

What is the best way to traverse the path values and determine the final value's ID?

From /News/Articles/Politics I expect to return 5.

Answer 1

I rarely recommend doing iterated calls on a database and this case is no different. Because your result set should be relatively small, you can afford to perform a full table query and process the multidimensional array to achieve your desired output.

Iterate the entries in your path and perform an iterated check for qualifying rows (matching Name and matching Father).

Since there can be only one qualifying row per inner loop, break as soon as it is found for best efficiency. ...just be sure to update the $id and $parent variables before doing so.

A check for "no qualifying rows" has been implemented, but it may or may not be relevant to your project. You can decide if you want this safety net.

Code: (Demo)

$resultset = [                                                     // query just once, collect the full tree
    ["ID" => 1, "Name" => "News", "Father" => 0],
    ["ID" => 2, "Name" => "Articles", "Father" => 0],
    ["ID" => 3, "Name" => "Politics", "Father" => 1],
    ["ID" => 4, "Name" => "Politics", "Father" => 2],
    ["ID" => 5, "Name" => "World", "Father" => 3],
    ["ID" => 6, "Name" => "World", "Father" => 4]
];

$path = "/News/Politics/World";
// $path = "/Articles/The Funnies/Garfield";                        // a test case that fails
// News: parent = 0, id = 1                                         // \
// Politics: parent = 1, id = 3                                     //  > the intended logic
// World: parent = 3, id = 5                                        // /

$cfg['categories_separator'] = "/";
$breadcrumbs = explode($cfg['categories_separator'], trim($path, "/"));
// var_export($breadcrumbs);                                        // see what is generated

$parent = 0;                                                        // default value
foreach ($breadcrumbs as $crumb) {
    $id = false;                                                    // set invalid value for success check
    foreach ($resultset as $row) {
        if ($row["Name"] == $crumb && $parent == $row["Father"]) {  // qualifying match
            $id = $parent = $row["ID"];                             // dual declaration
            break;                                                  // break inner loop, progress to next $crumb        
        }
    }
    if (!$id) {                                                     // inner loop failed to find qualifying match
        echo "Uh-oh, Broken Breadcrumb Path -- $crumb not found in $path
";
        break;                                                      // break outer loop, path is invalid
    }
    // echo "ID = $id for $crumb
";                                // uncomment to see progress
}
echo "ID = $id for $crumb
";                                       // echo the result

Output:

ID = 5 for World