0

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.

OuzoPower
  • 230
  • 2
  • 11

2 Answers2

0

You can also create a new function on MYSQL to get the names from the ids and use like the following:

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

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

jetblack
  • 600
  • 4
  • 10
  • Thank you mentioning this, but as on a shared hosting, I'm not sure that I can create new MYSQL functions. I also want to keep things as portable as possible between the local development and the production server. A client MYSQL function would be the kind of thing that I forget to transfer. I believe that I'm currently on the right track with pure PHP code and will post back soon. – OuzoPower Feb 16 '17 at 16:51
0

This is my home made solution, coded in PHP, which 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, i.e. 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, one can retrieve leaf nodes as well, however, in such case an undefined offset error arises at line $path = str_replace('.', ',', $descendants[0]['path']); for leaf nodes, as they have no descendants. Hence some improvement of the code remains possible.

OuzoPower
  • 230
  • 2
  • 11