Create nested pages-list from MySQL database using recursion with fixed ordering

Question

I am trying to create a nested pages-list from data stored in the MySQL database. I try to do this because users can order the pages (using JS-script nestedSortable) in the way they want and from that array, I can then create a menu with the items in the right order.

My pages have the following data stored:

page_id
parent_page_id
ordering
site_id
title

I retrieve the following from the database:

SELECT * FROM `pages` WHERE site_id = '".$iSite_id."' ORDER BY parent_page_id, ordering ASC;

If a page does not have a parent, the parent_page_id is 0.

So far so good, but I have a lot of problems understanding how a recursive function works, and because the amount of levels is (theoretically) endless, I can't work around creating this function.

This is what I have so far:

$aPagesMenu = $oPage_controller->return_pages_menu($iSite_id);

function create_menu_recursive($aPagesMenu) {
    foreach($aPagesMenu as $aPage) {
        if($aPage['parent_page_id']){
             $aMenu[$aPage['parent_page_id']][$aPage['page_id']] = $aPage['title'];
            //Recursive function call here?
        } else {
            $aMenu[$aPage['page_id']] = $aPage['title'];
    }
    return $aMenu;
}

$aRecursiveMenu = create_menu_recursive($aPagesMenu);

I get stuck at trying to understand how the recursive function returns its content into the array of the first level? How can I understand this and correctly nest one level into another?

I really wish to understand this, because it gives me a lot of delay and issues. Any help is welcome!

EDIT

Some data retreived from database:

array(6) {
  [0]=>
  array(4) {
    ["page_id"]=>
    string(3) "274"
    ["parent_page_id"]=>
    string(1) "0"
    ["menu_ordering"]=>
    string(1) "0"
    ["page_description"]=>
    NULL
  }
  [1]=>
  array(4) {
    ["page_id"]=>
    string(3) "278"
    ["parent_page_id"]=>
    string(1) "0"
    ["menu_ordering"]=>
    string(1) "1"
    ["page_description"]=>
    NULL
  }
  [2]=>
  array(4) {
    ["page_id"]=>
    string(3) "273"
    ["parent_page_id"]=>
    string(3) "274"
    ["menu_ordering"]=>
    string(1) "0"
    ["page_description"]=>
    NULL
  }
  [3]=>
  array(4) {
    ["page_id"]=>
    string(3) "275"
    ["parent_page_id"]=>
    string(3) "274"
    ["menu_ordering"]=>
    string(1) "1"
    ["page_description"]=>
    NULL
  }
  [4]=>
  array(4) {
    ["page_id"]=>
    string(3) "276"
    ["parent_page_id"]=>
    string(3) "275"
    ["menu_ordering"]=>
    string(1) "0"
    ["page_description"]=>
    NULL
  }
  [5]=>
  array(4) {
    ["page_id"]=>
    string(3) "277"
    ["parent_page_id"]=>
    string(3) "275"
    ["menu_ordering"]=>
    string(1) "1"
    ["page_description"]=>
    NULL
  }
}

This data should then be converted to:

273
--> 275
--> 274
    --> 276
    --> 277
278
etc..

Please show data what you getting from query. I will write recursive function. — Monty Khanna
– Monty Khanna, Commented Jan 11, 2016 at 12:21

Community · Accepted Answer · 2017-05-23 11:51:59Z

0

I have searched through older posts on stackoverflow and I think I have found what I was looking for: PHP Building Recursive Array from List

I have used the following code as recursive function:

private function buildTree($itemList, $parentId) {
    // return an array of items with parent = $parentId
    $result = array();
    foreach ($itemList as $item) {
        if ($item['parent_page_id'] == $parentId) {
            $newItem = $item;
            $newItem['children'] = $this->buildTree($itemList, $newItem['page_id']);
            $result[] = $newItem;
        }
    }

    if (count($result) > 0) return $result;
    return null;
}

I then called this function with the array I've got from the database:

buildTree($aPages, 0);

Which gave me the array :D

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Jan 11, 2016 at 15:29

PIDZB

9131 gold badge16 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create nested pages-list from MySQL database using recursion with fixed ordering

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related