Best way to generate text patterns from a PHP array having 3 columns (id, path, name)?

Question

For one possible solution, see my second post below.

The presence of a PHP array that stores data from a tree structure, with

• first column storing node identifier,
• second column, saving the path of the parent node as a concatenation of id values,
• third columns storing the name node,

which is the best way to create a text path (breadcrumbs) from a path executed by identifiers?

Record Examples

id | path | name --------------------- 1 | 0 | edible 14 | 1 | fruits 53 | 1.14 | apples 54 | 1.14 | pears 122 | 1.14.53 | red apples 123 | 1.14.53 | green apples 124 | 1.14.54 | yellow pears 

Input ID: 122

Corresponding input path: 1.14.53

Output line: edible > fruits > apples

The idea is to achieve something like:

 foreach($cats as $cat) { // for each category foreach(explode('.', $cat['path']) as $id) { // explode the path into chunks /* 1) get the name matching the value of $id 2) append label to breadcrumbs string */ } // 3) output breadcrumbs for the given category // [4) list leaf nodes under the breadcrumbs for the current category] } 

Nota bene: the array itself is generated by this MySQL / MariaDB query:

 $req = "SELECT c.id,p.path,c.name FROM `".$database['database']."`.`".$database['prefix']."productcategories` c LEFT OUTER JOIN `".$database['database']."`.`".$database['prefix']."prodcat_path` p ON c.id = p.id WHERE c.isparent AND (c.id=".$id." OR (p.path=".$id." OR p.path LIKE('".$id.".%') OR p.path LIKE('%.".$id.".%') OR p.path LIKE('%.".$id."'))) ORDER BY p.path ASC"; $res = mysql_query($req) or die(); 

The reason for saving the path in a separate table with a one-to-one relationship for records is because the trigger is used to calculate and save the path when inserting a new category.

Answer 1

You can also create a new function in MYSQL to get names from identifiers and use as shown below:

 SELECT p.id,YOURFUNCTION(p.path),..... 

https://dev.mysql.com/doc/refman/5.7/en/adding-functions.html

Answer 2

This is my homemade PHP encoded solution that works well:

 function generateBreadcrumbsForNodeAndDescendants($id) { define ('FOLDER_PATH',dirname(__FILE__).'/'); include(FOLDER_PATH.'db_ajax_connection.inc.php'); // Select node and all subnodes, excepted leafs // This query works, but is possibly not optimized. $req = "SELECT c.id,p.path,c.name FROM `".$database['database']."`.`".$database['prefix']."categories` c LEFT OUTER JOIN `".$database['database']."`.`".$database['prefix']."paths` p ON c.id = p.id WHERE c.isparent AND (c.id=".$id." OR (p.path=".$id." OR p.path LIKE('".$id.".%') OR p.path LIKE('%.".$id.".%'))) ORDER BY p.path ASC"; // We would add the following line to the WHERE clause if we wanted to retrieve leaf nodes too: // OR p.path LIKE('%.".$id."') $res = mysql_query($req) or die(); $descendants = array(); while($descendant = mysql_fetch_assoc($res)) { $descendants[] = $descendant; } $path = ''; // Get the path to the current node. // Because the records from the query are ordered by path, this is the first record. $path = str_replace('.', ',', $descendants[0]['path']); // This is because there is no record stored in the path table for the first-level nodes if ($path=='') $path = '0'; // Find ancestors of the current node $req = "SELECT c.id,p.path,c.name FROM `".$database['database']."`.`".$database['prefix']."categories` c LEFT OUTER JOIN `".$database['database']."`.`".$database['prefix']."paths` p ON c.id = p.id WHERE FIND_IN_SET(c.id,'".$path."')"; $res = mysql_query($req) or die(''); $ancestors = array(); while($ancestor = mysql_fetch_assoc($res)) { $ancestors[] = $ancestor; } // Build a list of all ancestors and descendants of the current node, ie concatenate arrays $nodes = array_merge($ancestors,$descendants); $paths = array(); // Build associative key => value pairs: (id => path) and (id => name) foreach ($nodes as $node) { $paths[$node['id']]=$node['path']; $names[$node['id']]=$node['name']; } $html=''; // for each "descendant" node (including custom "root" node), translate numeric path into breadcrumbs foreach ($descendants as $descendant) { $html .= '<p>'; $path = $paths[$descendant['id']]; if ($path) { // because no path is stored for the 1st level nodes, we must test that $path != '' $i = 0; foreach(explode('.', $path) as $id) { if ($i) $html .= ' > '; $html .= $names[$id]; // These nodes can also be encapsulated in html anchors <a href="">...</a> to become links. $i++; } $html .= ' > '; // optional if we want to add the element to the path of its parent nodes (see below) } $html .= $names[$descendant['id']]; // optional if we want to add the element to the path of its parent nodes // else, we should do some post-processing to remove duplicate paths, // as several nodes may have the same parent path. $html .= '</p>'; } echo $html; } generateBreadcrumbsForNodeAndDescendants((int) $_POST['key']); 

Note: by adding OR p.path LIKE('%.".$id."') At the end of the WHERE clause in the first query, you can also get leaf nodes, but in this case, an undefined offset error occurs in the line $path = str_replace('.', ',', $descendants[0]['path']); for leaf nodes, since they have no descendants. Consequently, some code improvement remains possible.