For one possible solution, see my second post below.
Having a PHP array storing data from a tree structure, with
- the first column storing the id of the node,
- the second column storing the path of the parent node as a concatenation of id values,
- the third columns storing the name of the node,
which is the best way to generate a text path (breadcrumbs) from the path done of ids?
Example records:
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 string: 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 storing the path into a distinct table, with a one to one relationship for records, is that a trigger is used to compute and store the path when a new category is inserted.
