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:

Description of table 'menu'

$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',
      ),
    ),
  ),
)

I think this is still a little too abstract and broad. Can you at least provide some real data to play with? Try var_export($array); to get something tangible to post on here. And shouldn't return $pad; be return $path; in your function? — Jeff Puckett
– Jeff Puckett, Commented Feb 7, 2017 at 23:17
You are right Jeff, $pad is of course $path in the return. I've added a piece of the array, thanks — DETLEVSKI
– DETLEVSKI, Commented Feb 8, 2017 at 10:18
If you use var_export instead of print_r it makes it so that we can easily copy/paste functional code. — Jeff Puckett
– Jeff Puckett, Commented Feb 8, 2017 at 13:55
I hope this makes my code more clear, I want to do a much as possible with PHP and arrays and not with SQL queries. The function should create an update of the database after every edit or creation of a menu item, so the menu field can be used to find the page ID. That is the plan at least, if the menu fields are updated after a change I can also use the field to create a sitemap in XML format. Creating the sitemap is not the issue though. — DETLEVSKI
– DETLEVSKI, Commented Feb 8, 2017 at 19:11
you've provided your bad output, and while that's helpful, I was asking for you to please provide your input. var_export($pages); not $tree thanks — Jeff Puckett
– Jeff Puckett, Commented Feb 8, 2017 at 22:14

Jeff Puckett · Accepted Answer · 2017-02-08 23:18:52Z

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) );

Jeff you provided me with the missing part, this will work just fine. Thanks! Recursion is difficult to get your head around, but is in this case the most efficient way to get things done, After flattening and looping I will have a perfect navigation table that can easily be modified for multi-lingual versions of a site. So thanks again!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related