Use multi-dimensional PHP array in to create friendly URLS for each menu item in MySQL database

Question

The MySQL navigation table uses id, parent_id, name to have an unlimited navigation depth. I am using a recursive function to create the multidimensional array, that works. What I have been trying for hours is to create a function that puts the path (/mainitem/subitem/subsubsitem/subsubsubitem/ etc in the menu_nl of the menu table:

$q = " SELECT m.id, m.parent_id, m.menu_nl, parents.menu_nl AS 'parent' FROM menu AS m LEFT JOIN menu AS parents ON parents.id = m.parent_id ORDER BY m.parent_id ASC, m.volgorde ASC";

$r = $dbcon->query($q); $pages = $r->fetchAll(PDO::FETCH_ASSOC);

function create_array(array $elements, $parentId = 0) {

    $branch = array();

    foreach ($elements as $element) {
        if ($element['parent_id'] == $parentId) {


            $children = create_array($elements, $element['id']);

            if ($children) {
                $element[$element['menu_nl']] = $children;
            }


            $branch[] = $element;
        }
    }

    return $branch;
}

$tree = create_array($pages);

A var_export looks this:

array (
  0 => 
  array (
    'id' => 34,
    'parent_id' => 0,
    'menu_nl' => 'INTRODUCTION',
    'parent' => NULL,
  ),
  1 => 
  array (
    'id' => 36,
    'parent_id' => 0,
    'menu_nl' => 'TEAM',
    'parent' => NULL,
    'TEAM' => 
    array (
      0 => 
      array (
        'id' => 133,
        'parent_id' => 36,
        'menu_nl' => 'Team member A',
        'parent' => 'TEAM',
      ),
      1 => 
      array (
        'id' => 134,
        'parent_id' => 36,
        'menu_nl' => 'Team member B',
        'parent' => 'TEAM',
      ),
    ),
  ),
  2 => 
  array (
    'id' => 50,
    'parent_id' => 0,
    'menu_nl' => 'PRODUCTS',
    'parent' => NULL,
    'PRODUCTS' => 
    array (
      0 => 
      array (
        'id' => 151,
        'parent_id' => 50,
        'menu_nl' => 'SUB PRODUCT A',
        'parent' => 'PRODUCTS',
        'SUB PRODUCT A' => 
        array (
          0 => 
          array (
            'id' => 154,
            'parent_id' => 151,
            'menu_nl' => 'SUB OF SUB PRODUCT A',
            'parent' => 'SUB PRODUCT A',
            'SUB OF SUB PRODUCT A' => 
            array (
              0 => 
              array (
                'id' => 156,
                'parent_id' => 154,
                'menu_nl' => 'SUB OF SUB OF SUB PRODUCT A',
                'parent' => 'SUB OF SUB PRODUCT A',
              ),
            ),
          ),
        ),
      ),
      1 => 
      array (
        'id' => 152,
        'parent_id' => 50,
        'menu_nl' => 'SUB PRODUCT B',
        'parent' => 'PRODUCTS',
        'SUB PRODUCT B' => 
        array (
          0 => 
          array (
            'id' => 155,
            'parent_id' => 152,
            'menu_nl' => 'SUB OF SUB PRODUCTB',
            'parent' => 'SUB PRODUCT B',
          ),
        ),
      ),
      2 => 
      array (
        'id' => 153,
        'parent_id' => 50,
        'menu_nl' => 'SUB PRODUCT C',
        'parent' => 'PRODUCTS',
      ),
    ),
  ),
  3 => 
  array (
    'id' => 40,
    'parent_id' => 0,
    'menu_nl' => 'CONTACT',
    'parent' => NULL,
    'CONTACT' => 
    array (
      0 => 
      array (
        'id' => 139,
        'parent_id' => 40,
        'menu_nl' => 'Contact form',
        'parent' => 'CONTACT',
      ),
      1 => 
      array (
        'id' => 143,
        'parent_id' => 40,
        'menu_nl' => 'How to get there',
        'parent' => 'CONTACT',
      ),
    ),
  ),
)

Answer 1

save the element's fullpath in the array as a concatenation of its parent's path with its own. Then pass that down as a parameter in your recursive function.

live demo

example repository

<?php

// add $parentpath parameter
function create_array(array $elements, $parentId = 0, $parentpath = '') {

    $branch = array();

    foreach ($elements as $element) {
        // set the fullpath from parent concatenated to this menu_nl
        $element['fullpath'] = "$parentpath/$element[menu_nl]";

        if ($element['parent_id'] == $parentId) {

            // pass the fullpath down to its children
            $children = create_array($elements, $element['id'], $element['fullpath']);

            if ($children) {
                $element[$element['menu_nl']] = $children;
            }

            $branch[] = $element;
        }
    }

    return $branch;
}

$pages = require 'pages.php';

print_r( create_array($pages) );

Array
(
    [0] => Array
        (
            [id] => 34
            [parent_id] => 0
            [menu_nl] => INTRODUCTION
            [parent] => 
            [fullpath] => /INTRODUCTION
        )

    [1] => Array
        (
            [id] => 36
            [parent_id] => 0
            [menu_nl] => TEAM
            [parent] => 
            [fullpath] => /TEAM
            [TEAM] => Array
                (
                    [0] => Array
                        (
                            [id] => 133
                            [parent_id] => 36
                            [menu_nl] => Team member A
                            [parent] => TEAM
                            [fullpath] => /TEAM/Team member A
                        )

                    [1] => Array
                        (
                            [id] => 134
                            [parent_id] => 36
                            [menu_nl] => Team member B
                            [parent] => TEAM
                            [fullpath] => /TEAM/Team member B
                        )

                )

        )

    [2] => Array
        (
            [id] => 50
            [parent_id] => 0
            [menu_nl] => PRODUCTS
            [parent] => 
            [fullpath] => /PRODUCTS
            [PRODUCTS] => Array
                (
                    [0] => Array
                        (
                            [id] => 151
                            [parent_id] => 50
                            [menu_nl] => SUB PRODUCT A
                            [parent] => PRODUCTS
                            [fullpath] => /PRODUCTS/SUB PRODUCT A
                            [SUB PRODUCT A] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 154
                                            [parent_id] => 151
                                            [menu_nl] => SUB OF SUB PRODUCT A
                                            [parent] => SUB PRODUCT A
                                            [fullpath] => /PRODUCTS/SUB PRODUCT A/SUB OF SUB PRODUCT A
                                            [SUB OF SUB PRODUCT A] => Array
                                                (
                                                    [0] => Array
                                                        (
                                                            [id] => 156
                                                            [parent_id] => 154
                                                            [menu_nl] => SUB OF SUB OF SUB PRODUCT A
                                                            [parent] => SUB OF SUB PRODUCT A
                                                            [fullpath] => /PRODUCTS/SUB PRODUCT A/SUB OF SUB PRODUCT A/SUB OF SUB OF SUB PRODUCT A
                                                        )

                                                )

                                        )

                                )

                        )

                    [1] => Array
                        (
                            [id] => 152
                            [parent_id] => 50
                            [menu_nl] => SUB PRODUCT B
                            [parent] => PRODUCTS
                            [fullpath] => /PRODUCTS/SUB PRODUCT B
                            [SUB PRODUCT B] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 155
                                            [parent_id] => 152
                                            [menu_nl] => SUB OF SUB PRODUCTB
                                            [parent] => SUB PRODUCT B
                                            [fullpath] => /PRODUCTS/SUB PRODUCT B/SUB OF SUB PRODUCTB
                                        )

                                )

                        )

                    [2] => Array
                        (
                            [id] => 153
                            [parent_id] => 50
                            [menu_nl] => SUB PRODUCT C
                            [parent] => PRODUCTS
                            [fullpath] => /PRODUCTS/SUB PRODUCT C
                        )

                )

        )

    [3] => Array
        (
            [id] => 40
            [parent_id] => 0
            [menu_nl] => CONTACT
            [parent] => 
            [fullpath] => /CONTACT
            [CONTACT] => Array
                (
                    [0] => Array
                        (
                            [id] => 139
                            [parent_id] => 40
                            [menu_nl] => Contact form
                            [parent] => CONTACT
                            [fullpath] => /CONTACT/Contact form
                        )

                    [1] => Array
                        (
                            [id] => 143
                            [parent_id] => 40
                            [menu_nl] => How to get there
                            [parent] => CONTACT
                            [fullpath] => /CONTACT/How to get there
                        )

                )

        )

)